Delete Broken Named Ranges in Excel Using VBA

Question

Part of a much larger macro involves the detection and deletion of Named Ranges that were unintentionally duplicated upon the Move/Copy of a sheet to the main workbook. These "broken" Named Ranges are indicated by the "Value" column in the Name Manager showing "#REF!".

I've attempted the deletion of these named ranges using the following macro:

Sub DeleteBrokenNamedRanges()
Dim NR As Name
Dim numberDeleted As Variant

numberDeleted = 0
For Each NR In ActiveWorkbook.Names
    If InStr(NR.Value, "#REF!") > 0 Then
        NR.Delete
        numberDeleted = numberDeleted + 1
    End If
Next

MsgBox ("A total of " & numberDeleted & " broken Named Ranges deleted!")

End Sub

Unfortunately, the return value is 0 and no Named Ranges were deleted. I played around with protect/unprotect and some parameters of InStr, but nothing has worked.

Side Note - The return of NR.Value is not #REF! or similar error code as expected, but is actually the =C:\Blahblah\blarg.xls path instead.

Any help on this would be greatly appreciated, thanks!

Answer 1

I don't think error codes exist as range values (not that I'm any authority on this). If valid ranges had errors other than #REF! it could be a problem, but I think something like

IsError(NR.RefersToRange)

Might be a good way to check for broken named ranges?