Autofil syntax to auto-complete the range

[John Keith]

When using the macro recorder to autofil...
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F75")

F75 was chosen as the end point because the data in Column E stopped at row
75.

Is there a way to code the autofil so that the end point is dynamic with out
having to calculate the ending row?

Something like...
Range("E3").AutoFill Destination:=Range(Range("E3"),
Range("E3").End(xlDown)) <- xlDown takes the autofil to row 65536. How do I
make it stop at 75?

 

[Per Jessen]

Hi John

Try this:

LastRow = Range("E3").End(xlDown).Row
Range("F3").AutoFill Destination:=Range("F3", Cells(LastRow, "F"))

Regards,
Per

 

[John Keith]

Thanks, that solved the problem.

I modified it slightly, once I saw the quick way to calculate the last row
used.

lRow = Range("D3").End(xlDown).Row
Range("E3").AutoFill Destination:=Range("E3:E" & lRow)

And at first forgot I was referenced the column "in front" of the
destination to get the last row. I.E. the last row of D... to use as the
last row in the destinatio Column E.

And I never have liked the Cell( ) style of range references.

--
Regards,
John

Hi John

Try this:

LastRow = Range("E3").End(xlDown).Row
Range("F3").AutoFill Destination:=Range("F3", Cells(LastRow, "F"))

Regards,
Per

 

[Per Jessen]

Hi John

Thanks for your reply. I'm glad to help.

Regards,
Per

Thanks, that solved the problem.

I modified it slightly, once I saw the quick way to calculate the last row
used.

lRow = Range("D3").End(xlDown).Row
Range("E3").AutoFill Destination:=Range("E3:E" & lRow)

And at first forgot I was referenced the column "in front" of the
destination to get the last row. I.E. the last row of D... to use as the
last row in the destinatio Column E.

And I never have liked the Cell( ) style of range references.