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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,536
Office Version
  1. 365
Platform
  1. Windows
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?
 

Miss__Excel

New Member
Joined
Aug 11, 2015
Messages
13
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!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,536
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
Glad you were able to figure it out.
 

Forum statistics

Threads
1,144,630
Messages
5,725,394
Members
422,623
Latest member
Dave52

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
Top