Writing VBA code to fill down to the end of a range which varies in length

Question

I am writing a macro to automate the completion of a report which is of the same columnar structure (i.e., the same fields in the same columns month after month). However, the number of records in the report changes from month to month.

In using the macro recorder to fill a particular column it generated this code:

Selection.AutoFill Destination:=Range("Z2:Z1092")

It appears that this code would only work for a report ending on row 1092. Is there a way to modify this code to make it dynamically update depending on the length of the report?

I was thinking that if I define a variable that is equal to the final row of the report and use that in place of the 1092 in that line of code that would do the trick. I am new to using VBA and any help is immensely appreciated.

Answer 1

you could try:

lngLastRow= ActiveSheet.UsedRange.Rows.Count

Selection.AutoFill Destination:=Range("Z2:Z" & lnglastrow)

but I suggest you avoid using the Selection object, as there is a significant performance hit when selecting cells. Instead just use the Range object to point to your cells