Delete Named Range with Value '#NAME?' or Refers To '=#NAME?'

Question

Unable to delete Named Range with #NAME?

How do I add to my VBA to delete this?

I have created the below code, it deletes all the named ranges in my workbook except for one, details are:

Name: _xlfn.IFERROR Value: #NAME? Refers To: =#NAME?

'Delete All Named Ranges
Dim xName As Name
On Error Resume Next
    For Each xName In ActiveWorkbook.Names
        xName.Visible = True
        If InStr(1, xName.RefersTo, "=#NAME?") > 0 Then xName.Delete
        If InStr(1, xName.RefersTo, "#REF!") > 0 Then xName.Delete
        If InStr(xName.Value, "#REF!") > 0 Then xName.Delete
        xName.Delete
    Next xName
On Error GoTo 0
End Sub```

Justyna MK Justyna MK · Accepted Answer · 2019-09-27T11:41:25

The trick is that xName.RefersTo will show the original formula instead of the evaluated value, e.g. =SomeFormulaThatDoesntExist instead of #NAME?

You can overcome it by using Evaluate(xName.RefersTo) which will return Error 2029 for #NAME? error. Furthermore, you can simply add IsError(Evaluate(xName.RefersTo)) condition to your code, something like:

If IsError(Evaluate(xName.RefersTo)) Then xName.Delete

Delete Named Range with Value '#NAME?' or Refers To '=#NAME?'

1 Answers