title: How can I copy and insert into a named range that contains merged cells using VBA?
body: <p>I would like to have a subroutine that is passed a named range and a number of rows, and copies that number of rows from the bottom of the range and inserts them into the named range at the bottom, becoming the new bottom of the named range. If I have a range named 'WellC1', depicted here:<a href="https://i.stack.imgur.com/P0a6L.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/P0a6L.png" alt="Starting range" /></a>
  I would like the function to copy the bottom n rows (in this case, 12) and paste them (or insert them) at the bottom, while keeping them within the named range, so it ends up like this:<a href="https://i.stack.imgur.com/soI4H.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/soI4H.png" alt="Ending range" /></a>
  It should only paste the formulas (which is what is shown here as the 11.47 and numbers on the right are formula results) and needs to copy <em>within</em> the named range so the process can be repeated.
  Im new to VBA and having severa problems with this. One is insert adds things above the insert point; to keep the new years' data in the named range I figured I could add the first row of the next well's data to the bottom of the range and insert there (so top row of Well C2 is actually the bottom row of Well C1s named range and inserting at it keeps the new Well C1 data in the well C1 range). I can get the insert-into-named-range thing to work fine if there's no merged cells, btw.
  The other is I can't figure out how to actually copy the 12 (or n) bottom rows of named range WellC1, because of the merged cells. Is there a way to copy the bottom n rows of a range and/or reference them even if some are merged? What I've been trying so far is tweaking out versions of this:</p>
  <pre><code>Sub Copy_Insert_Into_Named_Range( _
                      target_range As Range, _
                      Optional num_rows As Integer = 1)
  'This uses the top row of the next well range as the bottom of this named range to be able to use a copy/insert and keep it within the named range.
  
  Dim last_row As Range: Set last_row = target_range.Rows((target_range.Rows.Count) - 1)
  Dim top_row As Range: Set top_row = target_range.Rows((target_range.Rows.Count) - (num_rows + 1))
  Dim num_targ_columns As Integer: num_targ_columns = target_range.Columns.Count
  
  Dim upper_cell As Range: Set upper_cell = top_row.Cells(1)
  Dim lower_cell As Range: Set lower_cell = last_row.Cells(num_targ_columns)
  
  Dim copy_area As Range: Set copy_area = Range(upper_cell &amp; &quot;:&quot; &amp; lower_cell)
  
  copy_area.Copy
  last_row.Offset(1).PasteSpecial
  </code></pre>
  <p>I'm falling apart at the copy_area set, I think because of the merged cell being lower_cell. Is there a different way to select these 12 (or n) bottom rows of the named range? There's about 600 of these wells we do this to manually per project so I'd like to automate that. My core idea is have each well's area be a named range and step through each named range and run the copy/insert function for it. Its working in other projects that don't have merged cells. I appreciate any ideas!</p>