VBA "Activesheet" delete Named Ranges

[Shiseiji]

All the code I've found for removing worksheet defined names is similar to the code below and requires the name of the specific worksheet:

Sub RemoveWsRangeNames()
'7-21-22
    Dim rngName       As Name
    Dim ThisWb      As Workbook
    Dim ThisWs      As Worksheet
'
    Set ThisWb = ActiveWorkbook
    Set ThisWs = ActiveSheet
'
    With ThisWb
    With ThisWs
'---remove any old defined names ---
    For Each rngName In ThisWb.Names
      If InStr(1, rngName, "ws_2Users") Then
    rngName.Delete
    End If
    Next
End With 'ThisWs
End With 'ThisWb
End Sub

I can put this code at the beginning of each module, but a module I can call is of course a much better option.
Any ideas?

TIA

Ron

 

[Joe4]

If you put the code in a "General" module, instead of a specific Sheet module, then you should be able to call it from anywhere in your code (in any module) with a line in your code like:

Call RemoveWsRangeNames

 

[Georgiboy]

You could also trim the code down a bit, if you look carefuly you can see that 'ThisWs' is not being used.

Also i tend not to use the 'With' statement unless i am writing things more than once:

Sub RemoveWsRangeNames()
    Dim rngName As Name
   
    For Each rngName In ThisWorkbook.Names
        If InStr(1, rngName, "ws_2Users") Then
            rngName.Delete
        End If
    Next
End Sub

 

[Shiseiji]

Thanks, I'll give that a try. I'm a hack and tend to stick with what others have shown works. I'll remember that about "With" statements. You can see my notes to ensure I keep up with what they have been used for.

For Each rngName In ThisWb.Names
      If InStr(1, rngName, "ws_2Users") Then

Ops, that deletes all the workbook names!

Should have been:

For Each rngName In ThisWs.Names
      If InStr(1, rngName, "ws_2Users") Then

Ron

 

[Georgiboy]

I am not sure that you can loop through the names on a particular sheet, I have not done it.

As a workaround I have used:

Sub RemoveWsRangeNames()
    Dim rngName As Name
    Dim ws As String
    
    ws = ActiveSheet.Name
    For Each rngName In ActiveWorkbook.Names
        If rngName.RefersToRange.Worksheet.Name = ws Then
            rngName.Delete
        End If
    Next rngName
End Sub