On autofill to last row, getting Runtime Error 1004 'range of object'_Global' failed

Miss__Excel

New Member
Joined
Aug 11, 2015
Messages
13
Hi all,

Been struggling with this for a bit, figured it was time to ask for help.

I have a bunch of formulas in columns B through R.
I need to autofill these formulas from the second row (the first is headers) to the last row in my spreadsheet. The last row is dynamic based on data, so I've been using lastrow.

Here's my code:

Range("B2:R2").Select
Selection.AutoFill Destination:=Range("B2:R" & lastrow)

There's a bunch of stuff above it having to do with the formulas I need in columns B through R, but that seems to work just fine.

I should mention this was working a little while ago, but I tweaked some of the formulas in the columns (mostly, added an IF statement) and now, I'm getting Runtime Error 1004 'range of object'_Global' failed error on the Selection.AutoFill Destination:=Range("B2:R" & lastrow) line.

The "IF" statements have to do with the character in column R (Y/N), so I don't know if the problem is that column R is to the right of all the others. Autofilling manually (not via macro) works fine.

My VBA knowledge is basically nonexistent, so I appreciate any help you can give.

Thank you!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Where is your "lastrow" variable calculation?
What is the value of "lastrow" when this error occurs?
If you are not sure, just add a message box before your Autofill command:
Code:
MsgBox lastrow
Also, do you have any merged cells in column R, or any hidden/protected cells in column R?
 
Upvote 0
Hi Joe! Thank you for the quick response, appreciate it. Your questions made me find the problem. I did something to the code that deleted the line that set the last row. Obviously I then got an error when I tried to refer to it.

The mild Socratic questioning made this obvious. Thank you again!!
 
Upvote 0
You are welcome!
Glad you were able to figure it out.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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