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.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: