Deleting Multiple Range names in Excel

Gambler

I've been searching the net trying to find a macro that will delete all the old range names in my workbook. All the range names start "=#REF!" as they are linking to files that no longer exists. I cam accross the following but it doesn't work. Any help appreciated. Thanks


Sub DeletePhantomRangeNames()

For Each nm In ActiveWorkbook.Names
If nm.RefersTo Like "*REF!*" Then
nm.Delete
End If
Next nm

End Sub


P.S. I've used a macro to do this before but I can't for the life of me find the file

scottishlass

' This procedure deletes every named range in which the RefersTo
' argument contains #REF!, where #REF! begins at the second character
' and ends at the fifth.

Sub Delete_Every_Named_Range_With_A_Reference_Error()
Dim nm As Object
Dim mystr As String

' Loops through each name in the active workbook.
For Each nm In ActiveWorkbook.Names

' Check for #REF! in the defined name reference.
If InStr(1, nm.RefersTo, "#REF!") Then

' Delete the defined name if it is a match.
nm.Delete

End If
Next
End Sub

Gambler

scottishlass wrote: »

' This procedure deletes every named range in which the RefersTo
' argument contains #REF!, where #REF! begins at the second character
' and ends at the fifth.

Sub Delete_Every_Named_Range_With_A_Reference_Error()
Dim nm As Object
Dim mystr As String

' Loops through each name in the active workbook.
For Each nm In ActiveWorkbook.Names

' Check for #REF! in the defined name reference.
If InStr(1, nm.RefersTo, "#REF!") Then

' Delete the defined name if it is a match.
nm.Delete

End If
Next
End Sub

Thanks. Have tried but getting the same as the earlier macro.

Run-time error '1004':

That name is not valid

scottishlass

Ummm....

Do you have any global Workbook name objects with the same name as the local Worksheet name object you are trying to delete?

Gambler

scottishlass wrote: »

Ummm....

Do you have any global Workbook name objects with the same name as the local Worksheet name object you are trying to delete?

Ummm...........no idea

scottishlass

Ok: whats your file saved as and what are the names of your worksheets?