Autofil syntax to auto-complete the range

  • Thread starter Thread starter John Keith
  • Start date Start date
J

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?
 
Hi John

Try this:

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

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.

--
Regards,
John


Per Jessen said:
Hi John

Try this:

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

Regards,
Per
Click to expand...
 
Hi John

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

Regards,
Per

John Keith said:
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.
Click to expand...
 
Back
Top