Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 1 down vote accepted

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

share|improve this answer
Ohh, that is slick- thank you. I appreciate that man! – Richard Pullman Jul 10 '13 at 15:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .