excell spreadsheet: street address in one row of columnand city state in next row

[John Capizzi]

I received a spreadsheet that was imported from a mailing program It had name in first column, street address in next column in separate rows instead of the next column same row. I expanded column but the stayed in separate rows. How do I move the city state to same row in next column. This spreadsheet is over 200 rows.

 

[Don Guillett]

Try this where the name is in col A

Sub alignaddress()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -2
Cells(i, mc).Offset(1, 1).Copy Cells(i, mc).Offset(, 2)
Rows(i + 1).Delete
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message news:[email protected]...