When Excel stops you sorting, saying “Merged cells have to be identically sized”, how do you find the merged cells?

Question

Excel puts up this complaint when the range of cells you are trying to sort includes merged cells.

But if it's a large sheet how do you track down the merged cells?

Answer 1

Edit > Find > Format (specify merged cells) > Find All.

Or use a macro:

Option Explicit

Sub testme()
    Dim myCell As Range
    Dim resp As Long

    For Each myCell In ActiveSheet.UsedRange.Cells
        If myCell.MergeCells Then
            If myCell.Address = myCell.MergeArea(1).Address Then
                resp = MsgBox(prompt:="found: " _
                & myCell.MergeArea.Address & vbLf & _
                "Continue looking", Buttons:=vbYesNo)
                If resp = vbNo Then
                    Exit Sub
                End If
            End If
        End If
    Next myCell
End Sub