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

[Miss__Excel]

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!

 

[Joe4]

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:

MsgBox lastrow

Also, do you have any merged cells in column R, or any hidden/protected cells in column R?

 

[Miss__Excel]

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]

You are welcome!
Glad you were able to figure it out.