Convert a Column of Data into multiple rows

[introtologic]

I am trying to convert a column of data into multiple rows but cant
seem to figure out how to do it.

e.g.

a
b
c
d
e
f
g
h
i

I want this column to be broken down into rows of 3 items

a b c
d e f
g h i

Thanks in advance

 

[Gord Dibben]

Sub ColtoRows()
Dim rng As Range
Dim I As Long
Dim J As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For I = 1 To rng.Row Step nocols
Cells(J, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(I, "A") _
.Resize(nocols, 1))
J = J + 1
Next
Range(Cells(J, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
End Sub

Enter 3 in the inputbox to get the configuration you want.


Gord Dibben MS Excel MVP

 

[introtologic]

Thank you!

Both solutions worked excellent