Autofill with a dynamic range

Question

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

ActiveCell.FormulaR1C1 = _
    "=IF(RC[-16]="""",""MISSING"",IF(RC[-14]="""",""MISSING"",RC[-14]-RC[-16]))"

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 xlDown taking me to the 1 millionth row. Further, I've tried reducing sheet size, that works as well but is not a good solution.

This is what the code looks like:

Selection.AutoFill Destination:=ActiveCell.Range("A1:A35000"), Type:= _
    xlFillDefault

What I want to do is to either:

1. 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

2. more preferably, this would be done automatically by having the program already recognize the range to autofill.

I've checked through the solutions and can't figure out how to apply it to my situation.

Solution

I think you may be looking for the following ...

Dim ws as Worksheet
Set ws = Worksheets("Sheet1")

Dim usedRows as Long

'Option One (not recommended): USED RANGE METHOD 
usedRows = ws.UsedRange.Rows.Count

'Option Two (more robust) .END(xlUp) METHOD (Assuming you have your Data in column "RC")
usedRows = ws.Cells(ws.Rows.Count, "RC").End(xlUp).Row

'YOUR
'CODE
'HERE

Selection.AutoFill Destination:=ws.Range(Cells(1,1),Cells(usedRows,1)), Type:= _ xlFillDefault

if that column has the most used rows of any in your worksheet.

Thanks to @scott for pointing out the .End(xlUp) option's superiority :)