How can I make Selection.AutoFill to fill the right range?

[excelos]

Hello

I have the code:

Selection.AutoFill Destination:=Range("C2:C159800")
Range("C2:C159800").Select

But I want it to fill the column dynamically, corresponding to the next column's rows.
E.g., if the file has 100 rows, it should autofill up to 100, etc.

How can I do that?

Also, do I need the line Range("C2:C159800").Select at the end or the macro recorder produced it?

Thanks

 

[Joe4]

You can find the last row in another dynamically, like this (assuming column B is the column you are looking at):

Dim lr as Long
lr = Cells(Rows.Count,"B").End(xlUp).Row

Then you can reference the range like:

Range("C2:C" & lr)

You seldom need to actually select ranges to work with them. That is just a product of the Macro Recorder recording all literal actions (like the selection of ranges).
Your code actually will go faster without selecting them.