Deleting hidden name definitions with invalid names in Excel 2003

Question

I am using this macro:

http://support.microsoft.com/kb/119826

to try and clean up hidden names in my excel file. It works for most of the hidden names, but not for a few _123Graph names. I'm not sure where these names came from, but when I try to delete them I get a 1004 automation error.

The knowledge base mentions that names with spaces may cause an error. Is there a way to delete these?

Answer 1

I use the excellent Name Manager add-in to, erm, manage the named ranges in my workbooks, including all those pesky ones like the example you give that are automatically created by Excel when autofiltering etc. which aren't normally exposed.

It allows filtering of names by type, location, scope etc. and generally knocks the awful built-in dialog into next week.

Edit: If installing an add-in is out of the question then adding the following code in a standard module will allow you to loop through the names in the workbook and delete the offending items.

Sub deleteNamedRanges()
    Dim n As Name
    Dim a As Variant

    For Each n In ThisWorkbook.Names

        a = MsgBox("Do you want to delete the following name?:" & vbCrLf & vbCrLf & n.Name & " (" & n.RefersTo & ")", vbYesNo, "Delete ranges")
        If a = vbYes Then
            n.Delete
        End If
    Next n
End Sub

If there are a great many names then you should be able to modify this to suit your needs.