Autofil syntax to auto-complete the range
 

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?

--
Regards,
John

Autofil syntax to auto-complete the range
 

Hi John

Try this:

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

Regards,
Per

"John Keith" skrev i meddelelsen
...
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?

--
Regards,
John

Autofil syntax to auto-complete the range
 

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" wrote:

Hi John

Try this:

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

Regards,
Per

"John Keith" skrev i meddelelsen
...
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?

--
Regards,
John

Autofil syntax to auto-complete the range
 

Hi John

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

Regards,
Per

"John Keith" skrev i meddelelsen
...
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" wrote:

Hi John

Try this:

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

Regards,
Per

"John Keith" skrev i meddelelsen
...
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?

--
Regards,
John