Transposing

[Nanapat]

I have several hundred rows with names/addresses as follows (each line is a
row):

Mr. Smith
123 Sunshine Lane
Graver, TX

Ms. Johnson
334 Forest Brook Lane
P.O. Box 730
Oklahoma City, OK

How can I transpose the name/address rows into columns all at once? I'm
having a problem since some names/addresses are 3 rows, some are 4, and some
are 5 or 6. There is one blank row in between each name/address. Please
help because right now I'm doing these one name/address at a time!

 

[Joel]

You need a macro

Sub transpose()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = 1
ColCount = 2
For LoopCount = 1 To LastRow
If Range("A" & (RowCount + 1)) = "" Then
ColCount = 2
RowCount = RowCount + 1
Rows(RowCount).Delete
Else
Cells(RowCount, ColCount) = Range("A" & (RowCount + 1))
ColCount = ColCount + 1
Rows(RowCount + 1).Delete
End If
Next LoopCount
End Sub

 

[Nanapat]

Joel, thank you! It worked like a charm!

You need a macro

Sub transpose()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = 1
ColCount = 2
For LoopCount = 1 To LastRow
If Range("A" & (RowCount + 1)) = "" Then
ColCount = 2
RowCount = RowCount + 1
Rows(RowCount).Delete
Else
Cells(RowCount, ColCount) = Range("A" & (RowCount + 1))
ColCount = ColCount + 1
Rows(RowCount + 1).Delete
End If
Next LoopCount
End Sub