Deleting invisible range names - how?

[Kevryl]

Excel 2007

I have pasted a list of all range names (368 of them in all) and I find a
few that have been obsoleted through their location being cut out (eg
"=#REF!$H$1
"). These show ikn the pasted list but not in the range names editing
dialogue box.

Is there a way to delete them? "Edit" and "Delete" in Formulas / Name
Manager are greyed out.

 

[Gary''s Student]

This little macro loops over your defined names. If it finds #REF in
RefersTo, the name is deleted:

Sub dural()
Dim s1 As String, s2 As String, s3 As String
Dim s4 As String
s3 = "#REF"
For Each n In ActiveWorkbook.Names
s1 = n.Name
s2 = n.RefersTo
s4 = Replace(s2, s3, "")
If Len(s2) <> Len(s4) Then
ActiveWorkbook.Names(s1).Delete
End If
Next
End Sub

 

[Don Guillett]

Option Explicit
Sub deletenameswithREF()
Dim n As Name 'String
For Each n In ThisWorkbook.Names
'MsgBox n.RefersTo 'Name
If InStr(n.RefersTo, "#REF") > 0 Then n.Delete
Next
End Sub

 

[Kevryl]

Thank you to Gary"s Student and Don.

looks like this forum may be getting dismatled. Can't get into your replies
to respond individually.
Cheers