VBA code to select all merged cells

Merged cells can be frustrating when using VBA code, as they just don’t operate in the same way as normal cells.  Even when using Excel without VBA, merged cells cause  a whole host of frustrating error messages.

Even if you don’t use them, lots of other users do.  So you will encounter merged cells in other workbooks sent to you, there is no way to avoid them.

Unfortuately, in most circumstances, it isn’t as simple as just unmerging them.  That might make some unintended changes to the worksheet (though most of them are likely to be presentational changes).  Once we have identified the merged cells we can decide what approach to take which each of them.

Which cells are merged? How can we tell easily?  As it can be difficult to identify them, the following VBA code will select them all for you:

  • from the used range of the worksheet if only one cell is selected
  • from the selected range  if more than one cell is selected.
Sub SelectAllMergedCells()

Dim c As Range
Dim mergedCells As Range
Dim fullRange As Range
Dim rangeDescription As String

'Count cells in selection. If 1 cell then search used range
'otherwise search the selected range
If Selection.Cells.Count > 1 Then
    Set fullRange = Selection
    rangeDescription = "selected cells"
    Set fullRange = ActiveSheet.UsedRange
    rangeDescription = "active range"
End If

'Loop through each cell in the chosen range of active sheet
For Each c In fullRange

    If c.MergeCells = True Then

        'If find first cell set the variable, otherwise add
        'to existing list of cells
        If mergedCells Is Nothing Then
            Set mergedCells = c
            Set mergedCells = Union(mergedCells, c)
        End If

    End If


'Select the range of unlocked cells
If Not mergedCells Is Nothing Then
    MsgBox "There are no merged cells in the " _
        & rangeDescription & ": " & fullRange.Address
End If

End Sub

Now that all the merged cells are selected, its up to you what to do with them.

If you want to convert merged cells to Center Across Selection cells, check out this post.

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

1 thought on “VBA code to select all merged cells”

  1. If I am not mistaken, I believe replacing your For..Next loop in its entirety with the following code will make the macro more efficient (the code only loops the merged cells rather than each cell in the selection…

    Application.FindFormat.MergeCells = True
    With fullRange
    Set c = .Find(“”, SearchFormat:=True)
    If Not c Is Nothing Then
    First = c.Address
    If mergedCells Is Nothing Then
    Set mergedCells = c
    Set mergedCells = Union(mergedCells, c)
    End If
    Set c = .Find(“”, c, SearchFormat:=True)
    Loop While Not c Is Nothing And c.Address First
    End If
    End With


Leave a Comment