How to merge and unmerge cells with VBA

The basic methods

As in the ribbon's Merge & Center, only the upper-left value survives. When you merge from the user interface Excel shows a warning; from VBA you'll see that prompt too unless you turn it off with Application.DisplayAlerts = False. For the manual options see merging cells in Excel and Merge & Center.

Sub MergeHeader()
    With Worksheets("Report").Range("A1:F1")
        .Merge
        .HorizontalAlignment = xlCenter
        .Font.Bold = True
    End With
End Sub

Merge while keeping all values

To avoid losing data, gather the non-blank values, join them, clear the range, write the joined text to the first cell and then merge. This macro works on the current selection.

Sub MergeKeepValues()
    Dim c As Range, joined As String
    Const SEP As String = " "

    If Selection.Cells.Count < 2 Then Exit Sub
    For Each c In Selection.Cells
        If Len(CStr(c.Value)) > 0 Then
            If Len(joined) > 0 Then joined = joined & SEP
            joined = joined & CStr(c.Value)
        End If
    Next c

    Application.DisplayAlerts = False
    Selection.ClearContents
    Selection.Cells(1, 1).Value = joined
    Selection.Merge
    Selection.WrapText = True
    Application.DisplayAlerts = True
End Sub

Change SEP to vbLf to put each value on its own line inside the merged cell. Cells are read row by row, left to right. Numbers and dates are converted with CStr, so they take your system's default format rather than the cell's display format; use c.Text instead of c.Value if you want what the cell shows.

Merge repeated labels down a column

A common reporting job is to merge runs of identical values in column A, such as region names. Loop upwards so merging doesn't change rows you haven't checked yet.

Sub MergeRepeatsInColumnA()
    Dim ws As Worksheet, lastRow As Long, r As Long, startRow As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Application.DisplayAlerts = False
    startRow = lastRow
    For r = lastRow - 1 To 1 Step -1
        If ws.Cells(r, "A").Value <> ws.Cells(startRow, "A").Value Then
            If startRow > r + 1 Then ws.Range(ws.Cells(r + 1, "A"), ws.Cells(startRow, "A")).Merge
            startRow = r
        End If
    Next r
    If startRow > 1 Then ws.Range(ws.Cells(1, "A"), ws.Cells(startRow, "A")).Merge
    Application.DisplayAlerts = True
End Sub

Here startRow tracks the bottom of the current run. Merging identical values loses nothing visible, but the data underneath is gone: only the top cell keeps the label. Do this on a copy used for presentation, never on the sheet you analyse.

Unmerge every cell and fill the gaps

Merged cells break sorting, filtering, pivot tables and lookups. Before analysis, unmerge them and copy the label into every cell that was part of the merge.

Sub UnmergeAndFill()
    Dim c As Range, area As Range, v As Variant
    For Each c In ActiveSheet.UsedRange.Cells
        If c.MergeCells Then
            Set area = c.MergeArea
            v = area.Cells(1, 1).Value
            area.UnMerge
            area.Value = v
        End If
    Next c
End Sub

Once a range is unmerged, later cells in it are no longer merged, so the loop simply skips them. On very large sheets you can speed things up with Application.ScreenUpdating = False at the start and True at the end.

Gotchas

• Merging a range that partially overlaps an existing merged area fails or produces a larger merge than you expected; unmerge first.
• You can't merge cells inside an Excel table (ListObject).
• Protected sheets block Merge unless formatting is allowed.
• Use Center Across Selection (.HorizontalAlignment = xlCenterAcrossSelection) when you only want the look of a merged heading; it keeps cells separate.

Analistable doesn't modify your workbooks, so it never merges or unmerges cells. When it reads a sheet with merged cells only the top-left cell has a value, so unmerge and fill first (as above) for reliable answers. See the cells and columns guide for non-VBA options.

Run a macro safely

Merging and unmerging can't be undone with Ctrl+Z after a macro runs, because VBA clears the undo stack. Save a copy first, or test on a duplicate sheet. Store the macros in the workbook (save as .xlsm) or in your Personal Macro Workbook if you want them available in every file, and add them to the Quick Access Toolbar for one-click use.

If you're writing code for other people, check the selection type at the start (If TypeName(Selection) <> "Range" Then Exit Sub) so the macro doesn't error when a chart or shape is selected, and always restore DisplayAlerts even if an error occurs by using an error handler that sets it back to True.

Frequently asked questions

Can I merge cells in Office Scripts instead?

Yes. Office Scripts for Excel on the web has a range merge method, but the syntax is TypeScript rather than VBA.

How do I stop the 'keeps only the upper-left value' prompt in VBA?

Set Application.DisplayAlerts = False before .Merge and back to True afterwards. The data in other cells is still discarded unless you combine it first.

How do I check whether a cell is merged in VBA?

Use Range("A1").MergeCells, which returns True for any cell in a merged area. MergeArea.Address gives the full merged range.

What's the difference between Merge and Merge Across:=True?

Merge combines the whole range into one cell. Merge Across:=True merges each row of the range separately, like the ribbon's Merge Across.

Do these macros work in Excel for Mac?

Range.Merge, MergeCells, MergeArea and UnMerge are part of the standard Excel object model and work in desktop Excel for Mac. VBA doesn't run in Excel for the web.

Related guides

• How to merge cells in Excel
• How to use Merge & Center in Excel
• How to use Center Across Selection in Excel
• How to combine text from multiple cells in Excel
• How to combine two columns in Excel