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.

Answer 2

Excel 2007 and above resolves this issue... but a quick fix for Excel 2003 is:

Go to the Tools Menu - Options - General tab - Check R1C1 Reference Style Then hit "Ok".

You will then be prompted for a new name for each of the corrupted names.

...then you can go back and uncheck the R1C1 check box.