Gord -

Deleted them as per your instruction. Again, thanks a million. This savede
me hours of manual labor. I was prepared to enter the info manually into my
database if not for the workaround.

Mitch

"Gord Dibben" wrote:

Thanks for the feedback Mitch.

BTW....what did you do with the blanks?


Gord

On Fri, 23 Jun 2006 15:44:01 -0700, Mitch
wrote:

Gord -

An absolute winner! Your macro did exactly what I needed it to do. Thanks a
million

Mitch

"Gord Dibben" wrote:

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?



Gord Dibben MS Excel MVP