J
Joanne
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
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