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

Hi John

Try this:

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

Regards,
Per

"John Keith" <(E-Mail Removed)> skrev i meddelelsen
news:22E49DC1-185F-49B0-9AD5-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> skrev i meddelelsen
> news:22E49DC1-185F-49B0-9AD5-(E-Mail Removed)...
> > 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
>
>

Hi John

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

Regards,
Per

"John Keith" <(E-Mail Removed)> skrev i meddelelsen
news:7BFF86B0-8EA0-48CC-B1FC-(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i meddelelsen
>> news:22E49DC1-185F-49B0-9AD5-(E-Mail Removed)...
>> > 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
>>
>>