Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.