Hidden Named Ranges: Making Visible then Delete

[jacobrcotton]

Hello hello!

My company has a bad habit of recycling spreadsheets instead of going back to source templates. We are running into a problem of having thousands (yes, thousands. don't ask.) of hidden named ranges that reference (1) nothing at all, producing a "#REF!" error, (2) a website link, producing a value of "{...}" or (3) something else entirely resulting in another error.

My goal is one of two (or both?) macros.

(1) If a named range is hidden, then unhide it & delete it.

Sub DeleteHiddenNames()

    Dim wbName As Name

    For Each wbName In ActiveWorkbook.Names
        If wbName.Visible = False Then
            ActiveWorkbook.Names(wbName.Name).Visible = True
            ActiveWorkbook.Names(wbName.Name).Delete
        End If
    Next wbName
    
End Sub

I am throwing an error at line ActiveWorkbook.Names(wbName.Name).Delete. The error is "run time 1004: the name you entered was invalid".


(2) If a named range has a value that is an error or whatever {...} is, delete it.

Sub DeleteErrorNames()   
   Call UnhideAllNames

   Dim wbName As Name

    For Each wbName In ActiveWorkbook.Names
        If ISERROR(wbName.Value) or wbName.Value = "{...}" Then
            ActiveWorkbook.Names(wbName.Name).Delete
        End If
    Next wbName
End Sub

This code simply isn't doing anything.

Any guidance would be much appreciated.

 

[shg]

How about ...

Sub DeleteHiddenNames()
  Dim oName As Name

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