title: Autofill with a dynamic range
body: <p>I'm constructing a program to automate some work, using record and then cleaning. It has worked well up until a calculation that takes too long for me. Its a simple nested IF statement </p>
  
  <pre><code>ActiveCell.FormulaR1C1 = _
      "=IF(RC[-16]="""",""MISSING"",IF(RC[-14]="""",""MISSING"",RC[-14]-RC[-16]))"
  </code></pre>
  
  <p>We deal with data that can range from being only 10 rows up to a couple hundred thousand. My "solution" that I'm not happy with so far has limited the autofill to range A1:A35000 - which still takes Excel a bit to process. This was the solution to avoid <code>xlDown</code> taking me to the 1 millionth row. Further, I've tried reducing sheet size, that works as well but is not a good solution.</p>
  
  <p>This is what the code looks like:</p>
  
  <pre><code>Selection.AutoFill Destination:=ActiveCell.Range("A1:A35000"), Type:= _
      xlFillDefault
  </code></pre>
  
  <p>What I want to do is to either: </p>
  
  <ol>
  <li><p>autofill from a range referenced by a number in a given cell (so if the data i put is 500 rows I have a cell I type in 500 and all the autofills go from A1:A500), or</p></li>
  <li><p>more preferably, this would be done automatically by having the program already recognize the range to autofill. </p></li>
  </ol>
  
  <p>I've checked through the solutions and can't figure out how to apply it to my situation. </p>