Unmerge and Report: Break Apart Every Merged Cell in One Click

TL;DR: Merged cells are the #1 reason sorts break and filters fail in inherited workpapers. This macro scans every sheet, lists every merged cell by sheet name and count, then unmerges them all in one pass. The value survives in the top-left cell. Excel has no “unmerge all” command. Now you do.

The Problem

You inherit the Garcia Industries workpaper. You need to sort the fixed asset schedule by placed-in-service date — but Excel says “This operation requires the merged cells to be identically sized.” You can’t even sort.

You try to AutoFilter the trial balance by account type — same error. You scroll the sheet and realize the prior preparer merged three rows to label “Current Assets” as a section header. Then they did it again for “Fixed Assets,” “Current Liabilities,” and “Equity.” The JE Log has merged cells for each adjusting entry group. The Depreciation schedule has merged headers spanning four columns. You didn’t create any of these. But sorting, filtering, and VLOOKUP all refuse to work until every merge is gone.

Excel has a “Merge & Center” button and an “Unmerge Cells” button — but only for the cells you’ve selected. There is no “Find All Merged Cells → Unmerge All.” You’d have to hunt for each one individually across 20 tabs. That’s a two-hour chore for something a macro can do in half a second.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook with merged cells you want removed
Permission to change the workbook (unmerging is surface-level — values aren’t deleted)

What the macro does:

Scans every sheet in ThisWorkbook for merged cells
Lists affected sheets and their merged-cell counts in a confirmation MsgBox
On confirm, unmerges all merged cells — values remain in the top-left cell
Reports the final count of merge areas cleaned up

Limitations:

Only works on ThisWorkbook — the workbook containing the macro. Store in PERSONAL.XLSB and replace with ActiveWorkbook to run on any open file
Unmerging may affect layout formatting. Column widths and row heights remain — but centered text across selection becomes left-aligned in the top-left cell
Works on visible sheets only — very hidden sheets are still scanned. If a sheet is protected, the unmerge operation will fail. The error handler reports which sheet caused the problem and you can unprotect it manually
Merged cells in UsedRange only — cells outside the used range are skipped

#The Macro

Option Explicit

Sub UnmergeAndReport()
    ' ── Unmerge and Report ─────────────────────────────
    ' Two-pass macro: scans every sheet for merged
    ' cells, lists findings in a confirmation MsgBox,
    ' then unmerges all with one click. Values remain
    ' in the top-left cell of each merge area.
    '
    ' Sorts, filters, VLOOKUP — everything that breaks
    ' on merged cells works again after this runs.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim cell As Range
    Dim dict As Object          ' sheetName → merge-area count
    Dim seen As Object          ' tracks merge areas already counted
    Dim areaAddr As String
    Dim total As Long
    Dim sheetCount As Long
    Dim info As String
    Dim key As Variant
    Dim unmerged As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    Set dict = CreateObject("Scripting.Dictionary")
    total = 0

    ' ═══════════════════════════════════════════════════
    ' PASS 1: Scan and tally merge areas per sheet
    ' ═══════════════════════════════════════════════════
    For Each ws In ThisWorkbook.Worksheets
        sheetCount = 0
        Set seen = CreateObject("Scripting.Dictionary")

        For Each cell In ws.UsedRange
            If cell.MergeCells Then
                ' Identify the merge area by its address —
                ' this avoids double-counting cells within
                ' the same merged block
                areaAddr = cell.MergeArea.Address(False, False)
                If Not seen.Exists(areaAddr) Then
                    seen.Add areaAddr, True
                    sheetCount = sheetCount + 1
                End If
            End If
        Next cell

        If sheetCount > 0 Then
            dict(ws.Name) = sheetCount
            total = total + sheetCount
        End If
    Next ws

    ' ── No merged cells → bail early ───────────────────
    If total = 0 Then
        MsgBox "No merged cells found in this workbook.", _
               vbInformation, "Unmerge and Report"
        GoTo CleanUp
    End If

    ' ═══════════════════════════════════════════════════
    ' Build report and ask for confirmation
    ' ═══════════════════════════════════════════════════
    info = "Found " & total & " merged cell(s) across " & _
           dict.Count & " sheet(s):" & vbCrLf & vbCrLf
    For Each key In dict.Keys
        info = info & "  • " & key & " (" & dict(key) & ")" & vbCrLf
    Next key
    info = info & vbCrLf & _
           "Unmerge all? Values will remain in the " & _
           "top-left cell of each merged area."

    If MsgBox(info, vbYesNo + vbQuestion, _
              "Unmerge and Report") = vbNo Then GoTo CleanUp

    ' ═══════════════════════════════════════════════════
    ' PASS 2: Unmerge every area
    ' ═══════════════════════════════════════════════════
    unmerged = 0

    For Each ws In ThisWorkbook.Worksheets
        Set seen = CreateObject("Scripting.Dictionary")

        For Each cell In ws.UsedRange
            If cell.MergeCells Then
                areaAddr = cell.MergeArea.Address(False, False)
                If Not seen.Exists(areaAddr) Then
                    seen.Add areaAddr, True
                    ws.Range(areaAddr).UnMerge
                    unmerged = unmerged + 1
                End If
            End If
        Next cell
    Next ws

    ' ── Final report ───────────────────────────────────
    MsgBox "Unmerged " & unmerged & " cell(s).", _
           vbInformation, "Unmerge and Report"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description & _
               vbCrLf & vbCrLf & _
               "A sheet may be protected. Unprotect on '" & _
               ActiveSheet.Name & "' and try again.", _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Two-pass design: scan first, act second

This macro runs in two distinct passes. The first pass scans every sheet and counts merge areas without changing anything. If it finds zero, it bails out immediately with a clean message — no empty MsgBox asking for confirmation about nothing.

The second pass only runs if the user clicks Yes on the confirmation dialog. This pass re-scans and unmerges. Why re-scan instead of storing the merge area addresses from pass 1? Because the count only cares about per-sheet totals. Storing every merge area address across all sheets would require nested dictionaries and more complex data structures. A second scan is simpler, equally fast with ScreenUpdating off, and avoids managing a large address collection in memory.

#The `seen` dictionary prevents double-counting

A merged cell covering A1:C3 is nine individual cells that all report MergeCells = True. Without de-duplication, the counter would register nine merge areas on a single 3×3 block — wildly wrong.

The seen dictionary tracks the .Address of each MergeArea:

areaAddr = cell.MergeArea.Address(False, False)
If Not seen.Exists(areaAddr) Then
    seen.Add areaAddr, True
    sheetCount = sheetCount + 1
End If

MergeArea.Address(False, False) returns something like "A1:C3" — a compact string that uniquely identifies the merged block. Once seen, every subsequent cell within that same block is skipped.

#`UnMerge` preserves the value automatically

This is the most important property of the VBA UnMerge method and the reason no extra code is needed:

ws.Range(areaAddr).UnMerge

After this line executes:

All cells except the top-left are cleared (their content, if any, is gone)
The top-left cell retains its value and number format
The merged region is now nine individual unmerged cells

Excel’s native “Unmerge Cells” button (Home tab → Alignment → Unmerge) behaves identically. The macro is calling the exact same operation, just on every merge area at once instead of one at a time.

If the merged cell had a formula, it stays in the top-left cell. If it had a format like bold, center, borders — all preserved. The only change is that the cells are now individual.

#No confirmation for zero — a deliberate UX choice

If the workbook has no merged cells, the macro reports it and exits silently:

If total = 0 Then
    MsgBox "No merged cells found in this workbook.", _
           vbInformation, "Unmerge and Report"
    GoTo CleanUp
End If

A confirmation dialog asking “Found 0 merged cell(s). Unmerge all?” is absurd. It would train the user to click Yes by reflex — the opposite of what a confirmation gate is supposed to do. By skipping the dialog entirely when the count is zero, the macro only asks for attention when there’s actually a decision to make.

#Protected sheets and the error handler

If a sheet is protected, calling .UnMerge raises a runtime error. The CleanUp error handler catches it and reports a specific message:

MsgBox "Error " & Err.Number & ": " & Err.Description & _
       vbCrLf & vbCrLf & _
       "A sheet may be protected. Unprotect on '" & _
       ActiveSheet.Name & "' and try again.", _
       vbCritical, "Macro Error"

The macro is designed to be re-run after fixing the issue — unprotect the problem sheet, run again, and it picks up where it left off (all previously unmerged cells stay unmerged).

#Adapt It

Run on any open workbook instead of the macro workbook. Store the macro in your Personal Macro Workbook (PERSONAL.XLSB) and replace all references to ThisWorkbook with ActiveWorkbook:

For Each ws In ActiveWorkbook.Worksheets

If no workbook is open, ActiveWorkbook will raise an error. The error handler catches it and reports the problem.

Skip the cover sheet and notes. Some sheets intentionally use merged cells for presentation. Add a skip filter:

If ws.Name = "Cover" Or ws.Name = "Notes" Then GoTo NextSheet
' ... scanning code ...
NextSheet:

Place the skip check at the top of the For Each ws loop so those sheets are never scanned.

Unmerge only the active sheet. Replace the For Each ws loop with a single sheet target for a one-tab cleanup:

Set ws = ActiveSheet
' ... scan and unmerge ws only ...

Re-merge after the fact. If you unmerge cells and realize you need to re-apply some merges, use Ctrl+Z immediately after running the macro. VBA doesn’t support undo programmatically, but Excel’s native undo stack covers the last VBA UnMerge operation when it’s the only action. For multi-sheet unmerges, undo may only reverse the last sheet — check before relying on it.

See also: Row & Column Unhider — another “invisible formatting” cleanup. Merged cells break sorts; hidden rows and columns hide data entirely. Run both when you inherit a file.

Download the sample workbook (10 sheets — 5 with merged cells, 5 without)

xlsx 12 KB