Deleting invisible range names - how?
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.
|