Excel - Why is Naming a Merged Range different from Merging a Range and then Naming it?

Viewed 1758

I know, I know, merged ranges are horrible to work with. But anyway:

I discovered some funny behaviour, whereby I could copy/paste some Merged Ranges but not others. So I experimented.

Setup:

enter image description here

enter image description here

Already it's apparent that these ranges are not the same thing. The ranges that were named after they were merged only consist of the topLeftCell reference, whereas the ones merged after naming retain a reference to all cells.


Edit: Test Code

Option Explicit

Public Sub PerformTests()

    Const NAME_THEN_MERGE As String = "Name_Then_Merge"
    Const MERGE_THEN_NAME As String = "Merge_Then_Name"
    Const PASTE_NAME_THEN_MERGE As String = "Paste_Name_Then_Merge"
    Const PASTE_MERGE_THEN_NAME As String = "Paste_Merge_Then_Name"

    TestNames MERGE_THEN_NAME, PASTE_NAME_THEN_MERGE
    '/ Result: Error 1004, cannot do that to a merged cell


End Sub

Public Sub TestNames(ByVal copyName As String, ByVal pasteName As String)

    wsPasteTest.Activate

    Dim copyRange As Range, pasteRange As Range
    Set copyRange = wsPasteTest.Range(copyName)
    Set pasteRange = wsPasteTest.Range(pasteName)

    CopyPasteCell copyRange, pasteRange

End Sub

Public Sub CopyPasteCell(ByRef copyCell As Range, ByRef pasteCell As Range, Optional ByVal pasteRowHeights As Boolean = False)

    copyCell.Copy
    pasteCell.PasteSpecial xlPasteAll

    If pasteRowHeights Then
        Dim sourceRowHeight As Long
        sourceRowHeight = copyCell.rowHeight
        pasteCell.rowHeight = sourceRowHeight
    End If

End Sub

After extensive testing, this appears to be the conclusion:

If you name a range of cells and then merge them, the named Range retains a reference to all of the cells. If you merge first, the named range only refers to the topLeftCell.

If you try to copy a named range, it is treated as being the same size as its' reference. So, it is fine to copy a (named then merged) set of 5 cells to another set of merged cells of the same size, or to a single cell.

However, the (merged then named) range can only be copied to a single cell. Trying to copy to a set of 5 merged cells will result in a 1004 error.


Why? What is going on with how merged cells and named ranges are handled that causes this discrepancy?


For reference, this is Office 365, Excel Version 15.0.4805.1003

1 Answers
Related