Do you need the blanks cells?

If not, select column and F5SpecialBlanksOKEditDeleteEntire Row

Then run this macro.

If you want the blanks, leave them and enter "5" in the "columns desired.
Otherwise, enter "3"

Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
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


Gord Dibben MS Excel MVP


On Fri, 23 Jun 2006 09:55:02 -0700, Mitch
wrote:

I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?