Issue deleting named ranges

[knhelpme]

I have many excel files that have bad hidden named ranges (old files I'm trying to clean up for folks). I have tried all of the 'delete range names' suggestions, but I still keep running across a named range called "=#NAME?" - and so the code bombs at this point and nothing is deleted.

the last one I tried was this, where the error was oName= "=#NAME?"
Any help is much appreciated.

Sub DeleteHiddenNames()
Dim oName As Name

For Each oName In ActiveWorkbook.Names
If Not oName.Visible Then oName.Delete
Next oName
End Sub

 

[Alex Blakenburg]

Is it worth trying skipping over the error ?

Sub DeleteHiddenNames()
   Dim oName As Name

   On Error Resume Next
   For Each oName In ActiveWorkbook.Names
       If Not oName.Visible Then oName.Delete
   Next oName
End Sub

 

[knhelpme]

Thanks. This looks like it might skip over hidden ranges, and there are a ton of hidden range names referencing old files, etc that I need to clear. The files are taking forever to load. Folks keep copying tabs with the bad range names. I'm just trying to come up with a way to save all of these files without rebuilding.

 

[Alex Blakenburg]

There is no reason that should skip over hidden ranges. Have you tried it. If should should only skip those that cause the delete statement to error out.

Run another macro before and after that just counts hidden names.

 

[GWteB]

Imo it has nothing to do with hidden names, rather than ones that cannot be resolved, causing the #NAME? error.
This might be of some help.

Sub DeleteUnresolvedNames()
    Dim oName As Name
    Dim r     As Range

    For Each oName In ActiveWorkbook.Names
        On Error Resume Next
        Set r = oName.RefersToRange
        On Error GoTo 0
        If r Is Nothing Then
            If IsError(Evaluate(oName.Value)) Then
                oName.Delete
            End If
        End If
        Set r = Nothing
    Next oName
End Sub

 

[jkpieterse]

Sounds like you have some corrupted names in there. Have you tried analyzing your file using our (free) Name Manager?
Try temporarily turning on R1C1 mode, that sometimes forces Excel to look at your range names and offer to fix problem ones.

 

[knhelpme]

Thanks Alex and GWteB - the combo of your solutions worked GREAT.! I was able to delete the bad range names - and then show all named ranges to go back and delete the #NAME? Really appreciate it!

 

[Alex Blakenburg]

@knhelpme Thanks for the feedback. Glad we could help.

@jkpieterse Thanks for the link to the Name Manager, I will definitely try it out.