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.
