Move Text dynamically to another cell

**st4more** · 01-03-2010, 03:54 AM

Hi everyone!
FIRST OF ALL AND FOREMOST HAPPY NEW YEAR!!!

I would need help with the following:

I have an excel sheet with about a thousand company addresses that should be on the following format1
Column A
Companyname
Street Address
City
State Zip COde

Right now, the document looks like this: format2:
ColumnA ColumnB ColumnC ColumnD ColumnE
Companyname StreetAddress City State Zipcode

SO I would like to copy for each row all the cells B,C,D,E underneath column A
to make it looks like the format 1 above
I would need to skip a line underneath each address as well.
Thank you very much!
Help as soon as possible if possible

HAPPY NEW YEAR

**JBeaucaire** · 01-03-2010, 04:15 AM

This should do it, it will create the new list for you in column H:

Code:

Option Explicit

Sub ReformatAddresses()
'Author:    JBeaucaire
'Date:      1/3/2010
'Convert row format addresses into columnar format
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR
    Range(Cells(i, "A"), Cells(i, "E")).Copy
    Range("E" & Rows.Count).End(xlUp).Offset(2, 0).PasteSpecial xlPasteAll, , , True
Next i

Range("E1:E2").Delete xlShiftUp
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

**st4more** · 01-03-2010, 04:34 AM

HI
Thank you for your speedy response.
However there seems to be a slight mistake I think as the compiler tells me that a variable is not defined...
thank you.

**st4more** · 01-03-2010, 04:44 AM

Originally Posted by JBeaucaire

This should do it, it will create the new list for you in column H:

Code:

Option Explicit

Sub ReformatAddresses()
'Author:    JBeaucaire
'Date:      1/3/2010
'Convert row format addresses into columnar format
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR
    Range(Cells(i, "A"), Cells(i, "E")).Copy
    Range("E" & Rows.Count).End(xlUp).Offset(2, 0).PasteSpecial xlPasteAll, , , True
Next i

Range("E1:E2").Delete xlShiftUp
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

--------------------------------------------------------------------------------

HI
Thank you for your speedy response.
However there seems to be a slight mistake I think as the compiler tells me that a variable is not defined...
thank you.

**st4more** · 01-03-2010, 06:09 AM

HI
Thanks a lot. it worked in fact. I was using it in OpenOffice and it did some little trouble but it went fine in Excel.
However, I wanted to have cell D and E combined as the 4th and last line of the address. With your code, the zip which is column E is set apart as a 5th line. I wanted the state and zip to be combined in a 4th and last row. thank you for your help again.
Peace..

**JBeaucaire** · 01-03-2010, 01:55 PM

Code:

Option Explicit

Sub ReformatAddresses()
'Author:    JBeaucaire
'Date:      1/3/2010
'Convert row format addresses into columnar format
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR
    Range(Cells(i, "A"), Cells(i, "C")).Copy
    Range("H" & Rows.Count).End(xlUp).Offset(2, 0).PasteSpecial xlPasteAll, , , True
    Range("H" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(i, "D") & "  " & Cells(i, "E")
Next i

Range("H1:H2").Delete xlShiftUp
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

==========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)