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

 

[cjcass]

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

View attachment 90679

Ok, understood - I should probably leave them be!
Yes, I unhid them.
Thanks again for your time and help with this, much appreciated.

 

[Alex Blakenburg]

No problem. Glad we were at least of some help