I have some code that enters sets of 5-row data in column B, with the
sets of data being apart 41 (blank) rows. I would like to transpose
the data so the five rows become one row with five columns and all the
sets of data to appear after each other with no blank rows in-between.
Note that the first set of data can start at any row in the sheet (not
always row 2 as in the example).


Here's what I have
1 A B C
2 a1
3 b1
4 c1
5 d1
6 e1
7
8
9
10 a2
11 b2
12 c2
13 d2
14 e2

Here's what I need

1 A B C D E F G H
2 a1 b1 c1 d1 e1
3 a2 b2 c2 d2 e2
4