title: VBA Dynamic Range to Extend Rows
body: <p>I am trying to do what seems like a simple dynamic range task in VBA. </p>
  
  <p><a href="https://i.stack.imgur.com/dB0c3.jpg" rel="nofollow"><img src="https://i.stack.imgur.com/dB0c3.jpg" alt="enter image description here"></a></p>
  
  <p>The above table has data downloaded from an external website. New rows will be added to it every day. </p>
  
  <p><a href="https://i.stack.imgur.com/x1mN7.png" rel="nofollow"><img src="https://i.stack.imgur.com/x1mN7.png" alt="enter image description here"></a></p>
  
  <p>This second table references the first. So for example the Date column in this table will be =USA!A18.</p>
  
  <p>I want to write a VBA script so that whenever new data is added to the first table, I can run the macro to extend down the rows/formulas of the second table to add the new data. </p>
  
  <p>So if 5 new rows are added to the first table, the macro will extend the existing rows and formulas from columns A to W  in the second table down five. It cannot have any blank rows or #VALUE errors without breaking some other code in the spreadsheet. The data starts in A9 and extends to W9. </p>
  
  <p>I tried implementing the following:</p>
  
  <pre><code>Sub extendrows()
  
  Dim lastRow As Long
  
  lastRow = Worksheets("USA").Range("A" &amp; Rows.Count).End(xlUp)
  
  Worksheets("USA defprob").Range("A9:W9").AutoFill Destination:=Range("A9:W" &amp; lastRow)
  
  End Sub
  </code></pre>
  
  <p>but I get a memory error, which I suspect is because it's pasting several thousand blank rows instead of stopping. How can I adjust this code to do what I need the macro to do?</p>