Deleting invisible range names - how?

  • Thread starter Thread starter Kevryl
  • Start date Start date
K

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.
 
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
 
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
 
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
 
Back
Top