Try some code like the following:

Sub AAA()
Dim LastCell As Long
Dim Dest As Range
Dim R As Range
Dim WS As Worksheet

' Dest is where the records are written
Set Dest = Worksheets("Sheet2").Range("A1")
' WS is worksheet with columnar data
Set WS = Worksheets("Sheet1")
With WS
LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
' R is first cell of columnar data
Set R = WS.Range("A1")
Do Until R.Row LastCell
Do Until R.Text = vbNullString
Dest = R.Text
Set Dest = Dest(1, 2)
Set R = R(2, 1)
Loop
Set Dest = Dest(2, 1).EntireRow.Cells(1, "A")
Set R = R(2, 1)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 27 Jan 2009 04:47:02 -0800, Mishelley
wrote:

Good Morning,

I have the following that I need to move into rows (records) - same
worksheet is fine. How can I do this when the address blocks are different
sizes (some 3 and some 4 rows)? There is a blank row in between.

Thanks so much in advance!

Mishelley

Name
Address 1
City, State, Zip

Name
Address 1
Address 2
City, State, Zip

Name
Address 1
City, State, Zip

Name
Address 1
Address 2
City, State, Zip