Microsoft Office Tutorials and References
In Depth Information
Working with Range Objects
Working with merged cells
Working with merged cells can be tricky. If a range contains merged cells, you may need to take
some special action with the macros. For example, if cells A1:D1 are merged, the statement that
follows selects columns A through D (not just column B, as you might expect):
I don’t know if this unexpected behavior is intentional or whether it’s a bug. However, it can
cause your macro to behave in a manner that you didn’t expect. Merged cells can also cause
problems with sorting.
To determine whether a particular range contains any merged cells, you can use the following
VBA function. The function returns True if any cell in the argument range is a merged cell.
(Refer to Chapter 10 for more information about Function procedures.)
Function ContainsMergedCells(rng As Range)
Dim cell As Range
ContainsMergedCells = False
For Each cell In rng
If cell.MergeCells Then
ContainsMergedCells = True
To refer to merged cells, you can reference the entire merged range or just the upper-left cell
within the merged range. For example, if a worksheet contains four cells merged into one (A1,
B1, A2, and B1), reference the merged cells using either of the following expressions:
If you attempt to assign a value to a cell in a merged range that’s not the upper-left cell, VBA
ignores the instruction and doesn’t generate an error. For example, the following statement has
no effect if A1:B2 is merged:
Range(“B2”).Value = 43
Some operations cause Excel to display a confirmation message. For example, if A1:B2 is
merged, the following statement generates a message: This operation will cause
some merged cells to unmerge. Do you wish to continue?
Bottom line? Be careful with merged cells. Some have suggested that this feature wasn’t very
well thought-out before it was implemented. I tend to agree.