I have a question that is much like some that have already been posted, but
with a twist. I have a list of addresses that came off of the internet and
are all in one column. I need to transpose them into one row each, multiple
columns (ie, Name, Address, City, State, Zip, etc.), but the problem is that
they are not all currently the same number of rows. Some have faxes, some
don't. Some have 6 rows of info while others have 9. The commonalities are
that there is a blank row between each address, and each address has all of
the listed info in the same order (Name always comes fist, address second,
city, state zip third, phone fourth, fax fifth, etc.).

Is there a way to rearrange to at least get them into columns so I can come
back and do a little shifting around?

Thanks in advance.

Hi Craig,

The following should do the trick. It starts in A1 so you'll have to adjust
if necessary.

Option Explicit
Sub rearrange()

Dim curselection As Range
Dim i As Integer

Set curselection = Range("A1") 'or wherever you start

Do While curselection < ""

If Not curselection.Offset(1, 0) = "" Then
i = 1
Do
curselection.Offset(1, 0).Copy Destination:=curselection.Offset(0, i)
curselection.Offset(1, 0).EntireRow.Delete
i = i + 1
Loop Until curselection.Offset(1, 0) = ""
End If

curselection.Offset(1, 0).EntireRow.Delete
Set curselection = curselection.Offset(1, 0)

Loop

End Sub
--
-SA


"Craig" wrote:

I have a question that is much like some that have already been posted, but
with a twist. I have a list of addresses that came off of the internet and
are all in one column. I need to transpose them into one row each, multiple
columns (ie, Name, Address, City, State, Zip, etc.), but the problem is that
they are not all currently the same number of rows. Some have faxes, some
don't. Some have 6 rows of info while others have 9. The commonalities are
that there is a blank row between each address, and each address has all of
the listed info in the same order (Name always comes fist, address second,
city, state zip third, phone fourth, fax fifth, etc.).

Is there a way to rearrange to at least get them into columns so I can come
back and do a little shifting around?

Thanks in advance.

Brilliant! You just helped me skirt carpal tunnel for another day!

"StumpedAgain" wrote:

Hi Craig,

The following should do the trick. It starts in A1 so you'll have to adjust
if necessary.

Option Explicit
Sub rearrange()

Dim curselection As Range
Dim i As Integer

Set curselection = Range("A1") 'or wherever you start

Do While curselection < ""

If Not curselection.Offset(1, 0) = "" Then
i = 1
Do
curselection.Offset(1, 0).Copy Destination:=curselection.Offset(0, i)
curselection.Offset(1, 0).EntireRow.Delete
i = i + 1
Loop Until curselection.Offset(1, 0) = ""
End If

curselection.Offset(1, 0).EntireRow.Delete
Set curselection = curselection.Offset(1, 0)

Loop

End Sub
--
-SA


"Craig" wrote:

I have a question that is much like some that have already been posted, but
with a twist. I have a list of addresses that came off of the internet and
are all in one column. I need to transpose them into one row each, multiple
columns (ie, Name, Address, City, State, Zip, etc.), but the problem is that
they are not all currently the same number of rows. Some have faxes, some
don't. Some have 6 rows of info while others have 9. The commonalities are
that there is a blank row between each address, and each address has all of
the listed info in the same order (Name always comes fist, address second,
city, state zip third, phone fourth, fax fifth, etc.).

Is there a way to rearrange to at least get them into columns so I can come
back and do a little shifting around?

Thanks in advance.