deleted names in a Wbk still exist and refer to locations that don't exist, slow Excel

Question

In VBA Help for the RefersTo Property, they give this example of listing all the Names in a Wkb (fleshed out so you can run it as is)

 Sub showNames()'from VBA Help for "RefersTo"
   Dim newSheet As Worksheet
   Set newSheet = Worksheets.Add
   Dim i As Long, nm As Name
   i = 1
   For Each nm In ActiveWorkbook.Names
     newSheet.Cells(i, 1).Value = nm.Name
     newSheet.Cells(i, 2).Value = "'" & nm.RefersTo
     i = i + 1
   Next
   newSheet.Columns("A:B").AutoFit
 End Sub

When I run that on my current project, it turns up many Names that I thought were long gone. But here they are still hanging around and referring to places that no longer exist. I think this is what's slowing up my system and I'd love to get rid of those Names, but they don't show up in the Define Name window so where do I find them?

edit: Meant to mention that the Links item is greyed out for this Wbk.

Answer 1

Update

option 1
A manual method to delete corrupt names using R1C1 (I can recall JKP stating on another forum he had code to do this but he wasn't prepared to provide it for free)

1. Select Tools, Options and click the General tab.
2. Click the check box next to "R1C1 Reference Style", so that you change the current setting.
3. Press OK.
4. Excel will prompt you to change the name of any name (in all open workbooks!) that contains illegal characters.
5. Select Insert, name, define to delete the newly renamed names.
6. Set the R1C1 Reference style back the way you prefer using Tools, Options, General.

option 2
Chris Neilsen posted this at Any chance to delete programatically corrupt ranged names (with spaces) in Excel (2007/2010)

But, here's a possible alternative: SaveAs your workbook as a .xlsm You should get a dialog complaining about invalid names, with a option to rename and a Ok to All button. Once saved, close and reopen the file, Save As an .xls and you should be good to go

Initial Post
Download Name Manager which is the stand out addin by Jan Karel Pieterse and Charles Williams for managing names

It will handle Names that

• now error out as the ranges have been deleted (your issue),
• link to other Workbooks,
• are now corrupt

Plus it will convert global names to local sheet names, and vice versa and so on

Answer 2

- Updated Answer -

Since you know the names of the invalid ranges but can't see them in the Name Manager, you can try to delete them manually from the VBA Immediate window. The name you gave GrPix!patternListRange indicates a worksheet name so you should be able to delete it by typing

ActiveWorkbook.Names("GrPix!patternListRange").Delete

or

Sheets("GrPix").Names("patternListRange").Delete

in the Immediate Window

---

Original Answer

Have you tried deleting the invalid names via code? i.e.

For Each nm In ActiveWorkbook.Names
   If InStr(nm.RefersTo, "OldFileName.xls") > 0 Then
      nm.Delete
   End If
Next nm

Answer 3

Here are two more solutions that may work for others searching on this topic, but these still don't fix my own particular Workbook.

I'm still looking.

This is from Aaron Blood and shows the R1C1 method mentioned by brettdj:

 Sub RemoveDemonLinks()
   Dim wbBook As Workbook
   Dim nName  As Name
   Dim i      As Long

   Set wbBook = ActiveWorkbook

   i = 0
   If wbBook.Names.Count > 0 Then
      With Application
        .ReferenceStyle = xlR1C1
        .ReferenceStyle = xlA1
      End With

    For Each nName In wbBook.Name
       If InStr(nName.RefersTo, "#REF!") > 0 Then nName.Delete
       i = i + 1
    Next nName

    If i > 0 Then MsgBox i & " corrupted names was deleted from " & wbBook.Name
    End If
 End Sub

This is from MS Help

 ' Module to remove all hidden names on active workbook
    Sub Remove_Hidden_Names()

    ' Dimension variables.
     Dim xName As Variant
     Dim Result As Variant
     Dim Vis As Variant

     ' Loop once for each name in the workbook.
     For Each xName In ActiveWorkbook.Names

       'If a name is not visible (it is hidden)...
       If xName.Visible = True Then
           Vis = "Visible"
       Else
           Vis = "Hidden"
       End If

       ' ...ask whether or not to delete the name.
       Result = MsgBox(prompt:="Delete " & Vis & " Name " & _
           Chr(10) & xName.Name & "?" & Chr(10) & _
           "Which refers to: " & Chr(10) & xName.RefersTo, _
           Buttons:=vbYesNo)

       ' If the result is true, then delete the name.
       If Result = vbYes Then xName.Delete

       ' Loop to the next name.
   Next xName
 End Sub