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"
Else
    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
        Else
            Set mergedCells = Union(mergedCells, c)
        End If

    End If

Next

'Select the range of unlocked cells
If Not mergedCells Is Nothing Then
    mergedCells.Select
Else
    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.

---

What next?

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

The Excel Academy