To determine if 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

            Exit Function

        End If

    Next cell

End Function

To refer to merged cells, you can reference the entire merged range or just the upperleft 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:

Range("A1")

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 does not generate an error. For example, the following statement has no effect if A1:B2 is merged:

Range("B2").Value = 43