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

Sub ABC()
Dim rng As Range
Dim rw As Long, ar As Range
Set rng = Columns(2).SpecialCells(xlConstants)
rw = 2
For Each ar In rng.Areas
ar.Copy
Cells(rw, 3).PasteSpecial xlValues, Transpose:=True
rw = rw + 1
Next
Columns(2).Delete
End Sub

--
Regards,
Tom Ogilvy


"lt" wrote:

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

Tom, GREATLY appreciated!



Tom Ogilvy wrote:
Sub ABC()
Dim rng As Range
Dim rw As Long, ar As Range
Set rng = Columns(2).SpecialCells(xlConstants)
rw = 2
For Each ar In rng.Areas
ar.Copy
Cells(rw, 3).PasteSpecial xlValues, Transpose:=True
rw = rw + 1
Next
Columns(2).Delete
End Sub

--
Regards,
Tom Ogilvy


"lt" wrote:

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