This does get them into rows but since they are not consistently 4 lines and
there is a line between them the records are getting broken up. Is there a
way to have it just start a new row each time it hits a blank row rather than
defining "ROWS_PER_BLOCK"?

"Chip Pearson" wrote:


It depends on how those address blocks are laid out. Are there always
N number of rows per address? You might be able to adapt the following
code. Src is the source data, on sheet 3 starting in A1. Dest is the
destination, on sheet 2 starting in A1. It assumes that each address
block has ROWS_PER_BLOCK rows with no blank lines between the
addresses (an address block may have blank lines, but no blank lines
separate the blocks).

Sub AAA()
Dim Src As Range
Dim Dest As Range
Dim N As Long
Dim LastRow As Long
Const ROWS_PER_BLOCK = 4

Set Src = Worksheets(3).Range("A1")
Set Dest = Worksheets(2).Range("A1")
LastRow = Worksheets(3).Cells(Rows.Count, "A").End(xlUp).Row

Do Until Src.Row LastRow
For N = 1 To ROWS_PER_BLOCK
Dest(1, N) = Src(N, 1)
Next N
Set Src = Src(ROWS_PER_BLOCK + 1, 1)
Set Dest = Dest(2, 1)
Loop
End Sub

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


On Wed, 15 Oct 2008 06:44:01 -0700, Ratatat
wrote:

I have an excel file that contains 500+ address blocks because it was
originally a pdf file that I converted to excel format. My ultimate goal is
to do a mail merge from this file but that's not possible in the format it is
currently. Is there a way to pull the information from that sheet so that it
is organized into columns? Once it's there I can do text to columns to
separate items when necessary but I don't know how to get there.