Problem with Set Rng

[JMcD14]

Hi there,
I’ve been using the below code for some time to delete unwanted (grand total) rows from an often updated document:
Sub DeleteRows()
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
Dim rng As Range, cell As Range, del As Range
Set rng = Range("RowRemoval")
For Each cell In rng
If (cell.Value) = "Grand Total" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
It has been working fine for a number of weeks, however, when attempting to utilise it in another workbook (copied the module over exactly) I get the error message:
““Run-time error ‘1004’:
Method ‘Range’ of object ‘ _ Global’ failed”
And the: Set rng = Range("RowRemoval") is highlighted yellow.
It seems I am not designating my ranges correctly, but I cannot work out why it works in my previous workbook but not this one. Any recommended code or advice would be very much appreciated.
John

 

[craig.penny]

Hi JMcD14 and welcome

In the workbook that you copied this into, is there a range named RowRemoval?

 

[JMcD14]

Hi Craig,

Thank you very much

Yes I set the named range. I've managed a work around where I set the ranges via .values, rather than .select

Not ideal, but it works. I would still be interested in learning why though, as I will admit to not understanding...!

 

[craig.penny]

So when you go to the named range dropdown box just to the left of the formula bar on the worksheet, it lists RowRemoval? And you try running this code with that workbook active?

If yes then I'm stumped.