Copy, paste, transpose 4 cells delete and repeat

**blarsen** · 07-18-2012, 12:06 AM

I have a number of addresses in a single column (column A)
They are in this format:

Business Name
Addresss
City, State, Zip
(555)555-5555

There are a few thousand like that. What I need to do is to be able to copy it, past special and transpose it into the next available row. Then I want to delete those original four cells, and repeat.
Another way to describe what I am trying to do:
Make cells a1-a4 end up in b1- e1. Delete the contents of a1-a4 and have the next cells shift up into a1-a4. Copy, paste, transpose into b2-e2. Repeat.

I have had limited success recording a macro for this. Basically, it just copies into b1-e1, but then the next record is copied on top of it.

Any ideas?
I can provide anything necessary to make this work.

(I attached a spreadsheet to show what I'm looking to accomplish. Just make those cells transpose and end up in a row).

**MarvinP** · 07-18-2012, 12:36 AM

Hi blarsen,

You need to learn LastRow and how to get to it. See the video and look at the attached.
http://screencast.com/t/cirAzuHrZtMr

**blarsen** · 07-18-2012, 01:11 AM

I can't tell you how grateful I am for the help. You have gone above and beyond and saved me probably fifty hours of copying and pasting. I don't know how I can possibly thank you. You've gone above and beyond and I thank you from the bottom of my heart! The solution works flawlessly!

**MarvinP** · 07-18-2012, 01:16 AM

Hi,

Here are two things to help. Edit your original post and "Go Advanced" and change the Prefix to Solved. That tells others they don't need to watch my video.
Then click on the star under my post and approve of the answer, which gives me reputation points.

I'd also like to know if the video helped. It is only my third one and I need to know if the file was enough or does the video really help?