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

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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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...