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:
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

