Removing Named Ranges


Public Sub DeleteNamedRanges() 
Dim lcount As Long
Dim sname As Name
Dim smessage As String
   For lcount = ActiveWorkbook.Names.Count To 1 Step -1
      smessage = lcount
         
      On Error Resume Next
      Set sname = ActiveWorkbook.Names(lcount)
      
      If (InStr(sname.RefersTo, "#REF") > 0) Then
         smessage = sname.Name & " - deleted"
         ActiveWorkbook.Names(lcount).Delete
         Debug.Print smessage
      Else
         Debug.Print sname.Name
      End If

   Next lcount
End Sub


Workbook Specific

Public Sub DeleteNamedRanges() 
Dim sname as Name
   For Each sname in ActiveWorkbook.Names
      ActiveWorkbook.Names(sname.name).Delete
   Next sname
End Sub

Identifying worksheet level named ranges

For Each objName In ActiveWorkbook.Names 
   If objName.Name = worksheet!namedrange
Next objName


Worksheet Specific

Public Sub DeleteNamedRanges() 
Dim inamecount As Integer
   For inamecount = 1 To ActiveSheet.Names.Count
      ActiveSheet.Names(1).Delete
   Next inamecount
End Sub


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrev