Mike

This does not work as well as I would like but is
reasonable.

Make sure that there is a blank row above the addresses.
Select them and run the code.

Sub Transpose()
Dim c
Dim i As Integer, j As Integer, nr As Integer, count As
Integer
Dim rng As Range

'place values on rows
Set rng = Selection
nr = rng.Rows.count

For Each c In rng
c.Select
If IsEmpty(c) Then
j = 0: i = 0
ElseIf IsEmpty(c) = False Then
j = j + 1: i = i - 1
ActiveCell.Offset(i, j).Value = c.Value
End If
Next c
'delete column A
Columns("A:A").Select
Selection.Delete shift:=xlToLeft
'Get rid or redundant rows
For i = 2 To nr
Cells(i, 1).Select
If IsEmpty(ActiveCell) Then
Selection.EntireRow.Delete
i = i - 1
count = count + 1
If count = 10 Then
Exit Sub
End If
End If
Next i

End Sub

regards
Peter
-----Original Message-----
Hello, I have a fairly huge excel file in this format:

1 Bob Smith
2 123 fair lane
3 toledo, oh 12345-1234
4 Phone: (123) 456-4567
5 Fax: (123) 456-2345
6
7
8 Bob Smith
9 123 fair lane
10 toledo, oh 12345-1234
11 Phone: (123) 456-4567
12
13 Bob Smith
14 VP of nothing
15 123 fair lane
16 toledo, oh 12345-1234
17 Phone: (123) 456-4567

and on and on.

what I need is a macro that:
starts at A1
selects the range A1:A6 (whatever it's size)
copies it
moves to B1
paste:transpose
deletes rows 2-7

and runs again...

I'm stumped I've seen some offset stuff, but the
selection fo the
range baffles me.

some data is only 4 rows some is 12 rows. I can handle
moving the
data into the appropriate columns after this step is
done, unless
someone has an idea of putting phone and fax into
appropriate columns
(because they always begin with phone or fax), or @
symbol for e-mail
or , & - for city state zip.

any help would be appreciated.
.