VBA Merging cells to form 1 Address cell

[Lres81715]

I'ts gotta be an easy request but I can't seem to figure it out

I have a massive distribution list that I need to merge the multiple cells below the main line of information into one address cell. So the contents appear as one list. The simplified version of the the request appears like this

[TABLE="width: 929"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Company[/TD]
[TD]Address[/TD]
[TD]Goal[/TD]
[/TR]
[TR]
[TD]A, Nancy[/TD]
[TD]ABC Company[/TD]
[TD]1234 Main St[/TD]
[TD]1234 Main St Bakersfield, CA 93311[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bakersfield, CA 93311[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A, JD[/TD]
[TD]ABC Company[/TD]
[TD]12345 Parkplace[/TD]
[TD]12345 Parkplace Kirkland, WA 98033[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Kirkland, WA 98033[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A, Joe[/TD]
[TD]ABC Company[/TD]
[TD]12345 Pinnacle Dr[/TD]
[TD]12345 Pinnacle Dr POBox 3076 McLean, VA 22102[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]POBox 3076[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]McLean, VA 22102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A, Stephanie[/TD]
[TD]ABC Company[/TD]
[TD]12345 Main St, 2nd Floor[/TD]
[TD]12345 Main St, 2nd Floor POBOX 2301 West Des Moines, IA 50328[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]POBOX 2301[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]West Des Moines, IA 50328[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A, Steve[/TD]
[TD]ABC Company[/TD]
[TD]123 Aston Ave Ste 123[/TD]
[TD]123 Aston Ave Ste 123 Carlsbad, CA 92008[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Carlsbad, CA 92008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A, Vickee[/TD]
[TD]ABC Company[/TD]
[TD]12345 Main St, 2nd Floor[/TD]
[TD]12345 Main St, 2nd Floor Des Moines, IA 50328[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Des Moines, IA 50328[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A, Sarah[/TD]
[TD]ABC Company[/TD]
[TD]12345 W Wabash[/TD]
[TD]12345 W Wabash POBox 2803-01G Springfield, IL 62711 UNITED STATES[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]POBox 2803-01G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Springfield, IL 62711[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UNITED STATES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A, James[/TD]
[TD]ABC Company[/TD]
[TD]12345 Harrison St Fl 2ND[/TD]
[TD]12345 Harrison St Fl 2ND Oakland, CA 94612[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Oakland, CA 94612[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So cell C2 has the rest of the address filling in one row below and in the case of A, Sarah's company, multiple rows below it. the Goal is to combine all the information into one cell like in the last column. any idea how to do this?

 

[Michael M]

Try this on a copy of your worksheet.

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
For r = lr To 3 Step -1
    If Range("B" & r).Value = "" Then
        Range("C" & r - 1).Value = Range("C" & r - 1).Value & " " & Range("C" & r).Value
        Rows(r).Delete
    End If
Next r
End Sub

 

[Lres81715]

Try this on a copy of your worksheet.

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
For r = lr To 3 Step -1
    If Range("B" & r).Value = "" Then
        Range("C" & r - 1).Value = Range("C" & r - 1).Value & " " & Range("C" & r).Value
        Rows(r).Delete
    End If
Next r
End Sub

Yup! that was it. Forgot about going backwards in a step count. Thanks Michael

 

[Michael M]

glad to help...