VBA: How to delete Name Range based on Name + Scope

[losamfr17]

Hi,

I need a code to delete some duplicate Name Ranges which have identical names but have different scopes. For example, my file has two "List_Tabs" Name Ranges; I want to keep the one that has a Scope = Workbook and delete the one where the Scope refers to tab name "Info".

How do you delete a Name Range based on its name + its scope?

Thank you!

 

[ZVI]

Hi,
Try the below code:

Sub NamesAdd()
  ThisWorkbook.Names.Add "MyName", RefersTo:="Workbook name"
  ThisWorkbook.Sheets(1).Names.Add "MyName", RefersTo:="Sheet name"
End Sub
 
Sub DeleteSheetName()
  Dim n As Name
  For Each n In ThisWorkbook.Names
    If n.Name Like "*!MyName" Then n.Delete
  Next
End Sub
 
Sub DeleteAllSheetNames()
  Dim n As Name
  For Each n In ThisWorkbook.Names
    If Not n.Parent Is ThisWorkbook Then n.Delete
  Next
End Sub

 

[AlbertanFireFly]

Hi,
Try the below code:

Sub NamesAdd()
  ThisWorkbook.Names.Add "MyName", RefersTo:="Workbook name"
  ThisWorkbook.Sheets(1).Names.Add "MyName", RefersTo:="Sheet name"
End Sub

Sub DeleteSheetName()
  Dim n As Name
  For Each n In ThisWorkbook.Names
    If n.Name Like "*!MyName" Then n.Delete
  Next
End Sub

Sub DeleteAllSheetNames()
  Dim n As Name
  For Each n In ThisWorkbook.Names
    If Not n.Parent Is ThisWorkbook Then n.Delete
  Next
End Sub

How would I write the code if I want to delete all names scoped to a worksheet, except the "Print_Area" names to various worksheets?
I tried this, but it deleted all the Print_Area ones as well.

Sub DeleteAllSheetNames()
  Dim n As Name
  For Each n In ThisWorkbook.Names
    If Not n.Parent Is ThisWorkbook Then
        If Not n.Name Like "Print_Area" Then n.Delete
    End If
  Next
End Sub

 

[ZVI]

Hi,
Use this code line with asterik symbol: If Not n.Name Like "*Print_Area" Then n.Delete