Thank you Gord for your input. While I am not using your code it is in
my personal Excel 'help file' for future reference on how to move
around the cells and rows in a spreadsheet, especially concerning
staying in the same column but moving down the row - that bugger
really had me stymied.
You guys do a great service to us wannabes
Joanne

Gord Dibben wrote:

Joanne

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 GoTo endit
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
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
endit:
End Sub


Gord Dibben MS Excel MVP

On Tue, 25 Jul 2006 00:09:47 GMT, Joanne wrote:

I am still trying to get my labels in proper format.
They are in 3 lines, each line in a separate cell of the same column
I need to get each line in a separate cell in 3 adjacent columns. Such
as:
A1 John Doe
A2 100 Main St
A3 Anytown, USA 66666
to
A1 B1 C1
John Doe 100 Main St Anytown, USA 66666

The following code works, moving the data to the proper cells and
deleting the empty rows - but it only works on the first address then
quits.

Public Sub RowsToCols()
Dim r
Dim I
r = Range("A1").CurrentRegion.Rows.Count
For I = 1 To r Step 3
Range("A1").Offset(I - 1, 1).Value = Range("A1").Offset(I,
0).Value
Range("A1").Offset(I - 1, 2).Value = Range("A1").Offset(I + 1,
0).Value
Range("A1").Offset(I, 0).EntireRow.Delete
Range("A1").Offset(I, 0).EntireRow.Delete
Range("A1").Offset(I, 0).EntireRow.Delete

Next
End Sub

After doing the above job, I need to know how to access the next row
in Col A so that I can repeat the code on the next address, and on and
on until all addresses have been reformatted. I think I am in col 3 at
the end of the subroutine, so all I need to do is go down 1 row and
back to col A, run the code on the next address, go down 1 row and
back to col A again, run the code again etc etc

Any help on this sure would make my day
Thanks for the time and expertise
Joanne