Excel VBA – Macro to Merge Cells Based on Another Column

mergemicrosoft-excel-2007vba

I need help finding a macro that can merge cells across rows in a column if those same rows are already merged in another column. Below is a screenshot of what I have now that shows the cells that are merged in Column A.

before

The below screenshot is what I need the spreadsheet to look like after the macro runs; the corresponding cells in Column B are merged.

after

Best Answer

This was the shortest I could make. I tried it with your example and it worked for me.

Sub mergecolumn()

Dim cnt As Integer
Dim rng As Range
Dim str As String

For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    cnt = Cells(i, 1).MergeArea.Count
    Set rng = Range(Cells(i, 2), Cells(i - cnt + 1, 2))

    For Each cl In rng
        If Not IsEmpty(cl) Then str = str + vbNewLine + cl
    Next
    If str <> "" Then str = Right(str, Len(str) - 2)

    Application.DisplayAlerts = False
    rng.Merge
    rng = str
    Application.DisplayAlerts = True

    str = ""
    i = i - cnt + 1
Next i

End Sub
Related Question