Thanks in advance...

This is what I have

I have alot of data in column A
Basically they're addresses of clients
The problem is they are all bunched together in column A
The recipient information could have a range of 5 to 10 lines of data


This is what I want to do...

I want to be able to record a macro so that whatever group of cells I
choose, I want to be able to copy, select the next cell above the data and
paste special, then transpose.

For example..

Cell A1 is blank
Cells A2 through A6 have data
Cells A7 and A8 are blank
Cells A9 through A15 have data

I want to select Cells A2 through A6, copy, select A1, paste special and
traspose the data.

The problem is that when I record this it says I specifically select A1. I
don't want to specify any one cell, but the cell above the data so that it
can be transposed.

I've included the recorded macro below.

The whole idea behind this is that we get these client lists all the time
but they can be in text or word.doc files or xls format.
Even with the Replace features we do sometimes go through hundreds of client
addresses one by one, removing hard returns manually. I am trying to find a
faster way to organize the lists.
Which leads me to enter a manual line break (such as in the special replace
option in MS WORD)
With this I make sure there is a line or two between customers and past the
text into excel hoping I can use a macro below.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/12/2004 by Greegan
'
' Keyboard Shortcut: Ctrl+a
'
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub