vba to delete named ranges returning errors

[cjcass]

Hi,
I have the code below that successfully deletes all named ranges that are displaying #REF!
I tried adapting it to delete all named ranges that are displaying #NAME? by simply changing the #REF! to #NAME? in the code but alas it didn't work. Any thoughts on how this can be achieved?
Any help much appreciated.

For Each NR In ActiveWorkbook.Names
   If InStr(NR.Value, "#REF!") Then NR.Delete
   Next

 

[davesexcel]

#REF! and #NAME? are errors
Possibly,

For Each NR In ActiveWorkbook.Names
   If IsError(Range(NR)) Then NR.Delete
   Next

 

[Georgiboy]

Maybe with:

Sub test()
    Dim NR As Object
    For Each NR In ActiveWorkbook.Names
       If Split(NR, "!")(1) = "#REF" Or Split(NR, "!")(1) = "#NAME?" Then NR.Delete
    Next
End Sub

 

[cjcass]

#REF! and #NAME? are errors
Possibly,

For Each NR In ActiveWorkbook.Names
   If IsError(Range(NR)) Then NR.Delete
   Next

Hi, this one gave me this error...

 

[cjcass]

Maybe with:

Sub test()
    Dim NR As Object
    For Each NR In ActiveWorkbook.Names
       If Split(NR, "!")(1) = "#REF" Or Split(NR, "!")(1) = "#NAME?" Then NR.Delete
    Next
End Sub

Hi, this one gave me this error...

 

[Georgiboy]

Hi, this one gave me this error...
View attachment 90596

What line do you get the error on?

Are you using the whole code including the 'Dim NR As Object'

 

[cjcass]

What line do you get the error on?

Are you using the whole code including the 'Dim NR As Object'

Hi,
It's on the 3rd line down - If Split(NR, "!")(1) = "#REF" Or Split(NR, "!")(1) = "#NAME?" Then NR.Delete
Yes, using whole code.
Thanks.

 

[Alex Blakenburg]

Unless you have found a different way of creating the #NAME error, I am not sure you are going to get the intended result.
The only way I can get a #NAME error is to have a named range pointing to a cell that contains a #NAME error.
In this case then deleting the Range Name is not addressing the underlying issue.

If you want to proceed anyway then this might work for you.

Sub Delete_NameErrors()

    Dim NR As Name

    For Each NR In ActiveWorkbook.Names
        'If IsError(Range(NR.Name)) Then NR.Delete
        If Application.IfError(Evaluate(NR.Value), "DELETE_THIS") = "DELETE_THIS" Then
            NR.Delete
        End If
    Next

End Sub

 

[cjcass]

Unless you have found a different way of creating the #NAME error, I am not sure you are going to get the intended result.
The only way I can get a #NAME error is to have a named range pointing to a cell that contains a #NAME error.
In this case then deleting the Range Name is not addressing the underlying issue.

If you want to proceed anyway then this might work for you.

Sub Delete_NameErrors()

    Dim NR As Name

    For Each NR In ActiveWorkbook.Names
        'If IsError(Range(NR.Name)) Then NR.Delete
        If Application.IfError(Evaluate(NR.Value), "DELETE_THIS") = "DELETE_THIS" Then
            NR.Delete
        End If
    Next

End Sub

Thanks for your time and response, this one showed a Run-time error '13': Type mismatch on the line If Application.IfError(Evaluate(NR.Value), "DELETE_THIS") = "DELETE_THIS" Then.
As an example, the bottom four in the pic below are what I'm trying to delete:

 

[Alex Blakenburg]

Whoa ! that is a whole different story.
Those range names beginning with _xlfn are normally hidden. Did you unhide them ?
They seem to have something to do with backward compatibility.
Some posts ask about whether the workbook has been opened in in an older version but I just ran an unhide names on a new MS 365 workbook used to look at a question on this Forum that I know used a number of new formulas and it is full of "_xfln" #NAME entries.

Since Excel is creating these hidden entries I suggest leaving them alone.
When I tried to delete them, I got a 1004 error indicating it didn't like trying to access names starting with underscore.
That error might be new because I seem to recall having deleted them in the past to see what would happen and I think Excel just recreated them (but don't quote me on that)
PS: the x & y at the end are variables used in a Let function