How to tell VBA to stop pasting down a column?

[lizsunnysideup]

Hello. Just a quick question. Is there anyway to tell VBA to stop pasting my data by referring to end of another column for which there is data? I'm trying to paste info down a blank column and have told it to stop at row 200, but the data range always changes. For instance the end row may be 159 one day, 121 the next, etc. I set the code to 200 because it seems there are never more than 200 rows, but I'm not sure how set the range if it's constantly changing. Here's some of the code I have now.

   Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>0,RC[-1]<=14), TRUE)"
    Range("AI2").Select
    Selection.AutoFill Destination:=Range("AI2:AI200")
    Range("AI2:AI200").Select
    Rows("1:1").Select
    Range("O1").Activate
    Selection.AutoFilter
    End Sub

And for reference if it's possible to end pasting in column AI using column A because column A will always have a last active cell with data in it.

Thanks

 

[Rick Rothstein]

Change this part of your Range statements (although I am not sure why you are selecting the range after the AutoFill given you then turn around and select Row 1)...

"AI2:AI200"

to this...

"AI2:AI" & Cells(Rows.Count, "A").End(xlUp).Row)

 

[Fluff]

Or as the formula is looking at column AH

Range("AI2&AI" & Range("AH" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=IF(AND(RC[-1]>0,RC[-1]<=14), TRUE)"
Rows("1:1").AutoFilter

 

[lizsunnysideup]

Thank you both so much. Both of these worked!

 

[Fluff]

You're welcome & thanks for the feedback