VBA macro help variable vertical list blocks transposed to rows

[pdf]

Hi I need help with code for a macro that transposes blocks of vertical cells to rows, that are each separated by a blank row.

Please note that the vertical blocks vary in size.

ie:

A1 text1
A2 test2
A3 text3
A4
A5 text4
A6 text5
A7 text6
A8 text7
A9
...etc

Transposed, must look like:
A1 text1 B1 text2 C1 text3
A2 text4 B2 text5 C2 text6 D2 text7
...etc

Anyone please help!

 

[jimrward]

Re: VBA macro help varaible vertical list blocks transposed to rows

ok assuming the text is in col A, and we will write the new data from D1 downwards, try the following

Sub colToRow()
Dim LastRow As Integer
Dim j As Integer
Dim SaveJ As Integer

Dim Myvalue As String

Dim CurrValue

LastRow = Range("A65536").End(xlUp).Row
k = 0
l = 0
For j = 1 To LastRow
If Range("A" & j).Value <> "" Then
Range("D1").Offset(k, l).Value = Range("A" & j).Value
l = l + 1
Else
l = 0
k = k + 1
End If


Next j

End Sub

 

[pdf]

Wow! Thanks so much for the quick reply!

Worked like a charm!!!

CHEERS!

 

[Transpose]

Hi there,
I have a massive amount of data that I need to transpose.
It is currently in the following format:
Data requiring transposing is from column C to AZ (ie, 48 columns) and I have one row per day with a years worth of rows (ie, 365 rows).
I want to get all the data into one single vertical column, so that each day takes up 48 rows.
Is there a macro that can do this?
Please advise.
Thanks in advance.