Dynamic AutoFill Destination

[ilcaa]

Currently running a macro with an autofill destination.. Problem is that starting tommorrow the range of the fill will be completely different and each day download will be different from the next... How can I chnage the macro to autofill based on the current range no matter it length?

thanks..

ActiveCell.FormulaR1C1 = "=rankings "
Selection.AutoFill Destination:=Range("J2:J17")

 

[aca_041]

Try this one.

ActiveCell.FormulaR1C1 = "=rankings "
Selection.AutoFill Destination:=Range("J2:J" & range("A65536").end(xlup).row)

Assuming the last row in Column A is equal to the last row of the file. So you can chage it as needed. (ie B65536, C65536, etc.)

 

[njimack]

Do you actually need to use autofill?

Dim End_Row As Long
End_Row = Range("J" & Rows.Count).End(xlUp).Row
Range(J2:J" & End_Row).Formula = "=rankings "

By the way, is 'rankings' a defined name containing a formula, or simply a text string. If it's just text, you could simplify this to:
Range(J2:J" & End_Row) = "rankings"