try this code. It assumes each address has only 3 rows of data as in your
example. This code assumes you addresses are in Col. A and copies the
addresses to columns B through F, starting in row 1:
Sub ParseAddresses()
Const COL As Integer = 1 ' assumes column with addresses is Col. A
Dim astrCityStateZip() As String 'Array to hold split City, State and Zip
Dim x As Long, lngLastRow As Long, lngAddressCount As Long
Dim blnName As Boolean, blnAddress As Boolean, blnCityStateZip As Boolean
lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).Row ' Get last row
lngAddressCount = 1 ' Count of addresses copied to columns B through F
blnName = True 'All boolean values are False by default
' Loop through all rows, from the top
For x = 1 To lngLastRow
If Cells(x, COL) <> "" Then
If blnName Then
Cells(lngAddressCount, COL + 1).Value = Cells(x, COL).Value
blnName = False
blnAddress = True
ElseIf blnAddress Then
Cells(lngAddressCount, COL + 2).Value = Cells(x, COL).Value
blnAddress = False
blnCityStateZip = True
ElseIf blnCityStateZip Then
astrCityStateZip = Split(CStr(Cells(x, COL).Value), ",")
Cells(lngAddressCount, COL + 3).Value = astrCityStateZip(0)
Cells(lngAddressCount, COL + 4).Value = astrCityStateZip(1)
Cells(lngAddressCount, COL + 5).Value = astrCityStateZip(2)
blnName = True
blnCityStateZip = False
lngAddressCount = lngAddressCount + 1 'increment the row for
results
End If
End If
Next x
End Sub
