Excell Macro help

[dfkelly42]

I have text in a spreadsheet, not wrapped text but single line per row.
I wish to create a macro that will copy and paste th
address/state/zip/phone info into seperate cols to the right of th
name.
I have created a macro that will only paste to Row #1.

HOW.... do I designate the selected box(ie name #2 or cell #A2) to b
the starting point for the macro as I work down the page.

(Current)
Bob Smith
1234 turkey Ave
Putsville, UT
12345
(999)999-9999

Bill Smith
1235 Hen peck road
butte, Mt
65432
(999) 555-5555

(Wanted in 5 cols)
Bob Smith/1234 turkey ave/putsville, UT/12345/(999)999-9999
Bill Smith/1235 Henpeck Rd/etc...........get the idea?

The macro will cut and paste in different cells on the same row bu
repeats itself to the original row when I procede down the page.
I built a macro but it is tied to the original cell number.
How do I lose the cell number distinction so I can work my way down th
document. I have 300 of these....
PLEASE HELP,
Do

 

[Greg Wilson]

Don,

Suffice to say, ensure that you have a backup of your data
before executing this macro.

The macro keys off of the active cell and works its way
down the active cell column. Select a cell in the first
block of data that you want to change. I assume this is at
the top of the list. The macro programmatically transfers
the data and then deletes the unnecessary rows. It does
not select nor copy and paste. There is no need to.

Sub TransposeToCols()
Dim Rng As Range, C As Range
Dim i As Integer
Set Rng = ActiveCell.CurrentRegion
i = 0
Application.ScreenUpdating = False
Do Until Rng(1) = ""
For Each C In Rng
i = i + 1
Rng(1, i) = C
Next
Range(Rng(2), Rng(i + 1)).EntireRow.Delete
Set Rng = Range(Rng(2), Rng(2).End(xlDown))
i = 0
Loop
Application.ScreenUpdating = True
End Sub

Regards,
Greg

 

[dfkelly42]

To Greg Wilson,

1000 camels laden w/ treasure awaits you in heaven.
That was exactly what I had hoped for.
Your Macro script worked like a charm,
Thank you thank you thank you.....
Do