Transpose one column into multiple rows

[ghost123]

Hi,
i am working on a huge database, let say i have column A, and it lists

a1
a2
a3
a4



a5
a6


a7
a8
a9



and so on

how do i transpose them into columns (there are three space between them) like:
a1 a2 a3
a5 a6
a7 a8 a9
and so forth.

thanks in advance

 

[VoG]

Try this macro

Sub TrsPose()
Dim Area As Range, i As Long
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each Area In Range("A1:A" & LR).SpecialCells(xlCellTypeConstants).Areas
    i = i + 1
    Range("B" & i).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
End Sub

 

[ghost123]

thanks, it works, you the man