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

If no blanks between sets.......enter 3 when the InputBox asks for number of
columns.

If a blank between sets......enter 4


Gord Dibben MS Excel MVP

On Fri, 1 Aug 2008 18:17:01 -0700, robw150956
wrote:

Hi,
I have data(500 companies) that looks like this;
business name.
Address.
phone
business name.
Address.
phone
and I wat to convert it to this; (automatically)
business name,Address,phone
business name,Address,phone

can any one tell me how ?
thanks in advance