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

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,459
Office Version
  1. 365
Platform
  1. Windows
You can find the last row in another dynamically, like this (assuming column B is the column you are looking at):
Code:
Dim lr as Long
lr = Cells(Rows.Count,"B").End(xlUp).Row
Then you can reference the range like:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,053
Messages
5,526,502
Members
409,704
Latest member
saialkesh

This Week's Hot Topics

Top