title: Excel - Why is Naming a Merged Range different from Merging a Range and then Naming it?
body: <p>I know, I know, merged ranges are horrible to work with. But anyway:</p>
  
  <p>I discovered some funny behaviour, whereby I could copy/paste some Merged Ranges but not others. So I experimented.</p>
  
  <p>Setup:</p>
  
  <p><a href="https://i.stack.imgur.com/vjCpk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vjCpk.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/DViRc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DViRc.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <hr>
  
  <p>Edit: Test Code</p>
  
  <pre><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
  </code></pre>
  
  <hr>
  
  <p>After extensive testing, this appears to be the conclusion:</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <hr>
  
  <p>Why? What is going on with how merged cells and named ranges are handled that causes this discrepancy?</p>
  
  <hr>
  
  <p>For reference, this is Office 365, Excel Version 15.0.4805.1003</p>