Data in rows into columns (transpose, macro codes needed)

[fazraza81]

I currently have data in cells A1-A21228 (all in 1 column). I need it to go from:

Column A
Data line 1
Data line 2
Data line 3
Data line 4
Data line 5
Data line 6
Data line 7
Blank row(space)
Data line 1
.....
and so forth

to:

Column A Column B Column C Column D Column E Column F Column G
Data line 1 Data line 2 Data line 3 Data line 4 Data line 5 Data line 6 Data line 7

Please help me with the right macro code.

Also, in between each data 'item' there is a blank row(space). So the 8th row is blank, then a new data 'item' starts again on the '9th row', for another 7 rows, and so forth.

Please help! Thank you so much

 

[rpaulson]

Sub do_it()

c = 2
wr = 2

For r = 2 To Range("A" & Rows.Count).End(xlUp).Row

Cells(wr, c) = Cells(r, "A")

If Cells(r, "A") = "" Then
    wr = wr + 1
    c = 2
    GoTo 99
End If


c = c + 1


99 Next r
End Sub

 

[fazraza81]

Thank you so much for your help!

Question - If the data is not uniform, meaning the transpose doesn't all work (not always 7 rows of data). Can we put an IF statement to say if there are more or less than 7 rows, then start with the next 'item'? Thank you

 

[rpaulson]

It is already setup to work with any number of data row, it will transpose the list every time it finds a space in coulum "A"