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.

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
--
Gary''s Student - gsnu201003


"Kevryl" wrote:

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.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kevryl" wrote in message
...
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.

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

"Kevryl" wrote:

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.