Autofill Code

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
I used the recorder to auto fill. I noticed that it selected a range. This Macro needs to be universal and by selecting the range (K2:K11669) if the next file has more lines of data it will not autofill all the way to the bottom of the data.
Here is my current code:

Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K11669")
Range("K2:K11669").Select
Range("K2").Select

If the next file has 14,000 lines the code it will stop at 11,669. I am not sure how to write the code properly. Any help would be appreciated. Thanks
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
If there is a contiguous range in column J and you want to autofil the formula held in a cell in column K, then you could use the following code snippet (assuming that the cell with the formula you want to autofil is the one selected):

Code:
With Activecell
      .Autofill Destination:=Range(.cells(1,1), .cells(1,1).Offset(1,-1).End(xlDown).Offset(0,1))
End With

Best regards

Richard

Edit: Incomplete code!
 
Upvote 0
Very nice....I like it...It works great. How would I edit this to autofill a couple of columns....for instance J:M. If there is not an easy way I will just use this code multiple times. Thanks for the help
 
Upvote 0
That should be easy - J to M would be 4 columns so you would use:

Code:
With Activecell 
      .Autofill Destination:=Range(.cells(1,1), .cells(1,1).Offset(1,-1).End(xlDown).Offset(0,4)) 
End With

It is the final argument in the last Offset that you need to amend to cover multiple columns - hope this makes sense!

Best regards

Richard
 
Upvote 0
I thought that the Offset was refering to the xlDown?

I changed the code:

Range("B2:D2").Select
With ActiveRange
.AutoFill Destination:=Range(.Cells(1, 1), .Cells(1, 1).Offset(1, -1).End(xlDown).Offset(0, 3))
End With


This did not work. I got an "Run Time Error '424'" ........Object required.


Any Ideas?
 
Upvote 0
Sorry about that. I tried to use "ActiveRange" instead of "ActiveCell". I was not sure if that existed, thanks for the clarification. I have changed it back to ActiveCell and there was a runtime error (1004) (Autofill method of range class failed).

I inserted your code:


LastRow = Range("A65536").End(xlUp).Row

Range("B2:D2").Copy Range("B2:D" & LastRow)




This works very nicely. Thanks for your help
 
Upvote 0
bschulze said:
Sorry about that. I tried to use "ActiveRange" instead of "ActiveCell". I was not sure if that existed, thanks for the clarification. I have changed it back to ActiveCell and there was a runtime error (1004) (Autofill method of range class failed).

I inserted your code:


LastRow = Range("A65536").End(xlUp).Row

Range("B2:D2").Copy Range("B2:D" & LastRow)




This works very nicely. Thanks for your help

For future compatability, replace

Range("A65536").End(xlUp).Row

with one of

Cells(Rows.Count,"A").End(xlUp).Row
Cells(Rows.Count,1).End(xlUp).Row
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top