How do you clear all Named ranges on a particular Worksheet within a workbook.

Public NameX as name

    On Error Resume Next
        For Each NameX In ActiveWorkbook.Names
            NameX.Delete
        Next
     On Error Goto 0

The above works great for clearing all named ranges within the entire workbook but how do you clear names on only certain worksheets within the workbook?

I have tried the below but I am running into errors

Public WS as worksheet

    On Error Resume Next
         For Each WS In Worksheets
            Select Case WS.Name
              Case "Lookups", "YTD Financial Summary", "Info" ' add names of sheets NOT to clear
                Case Else
                    WS.Select
                    If WS.AutoFilterMode = True Then: ActiveSheet.AutoFilterMode = False
                        For Each NameX In WS.Names
                            NameX.Delete
                        Next
                    ActiveWindow.FreezePanes = False
            End Select
        Next
    On Error GoTo 0
LVL 2
bearblackGlobal Program ManagerAsked:
Who is Participating?
 
Shanan212Connect With a Mentor Commented:
         For Each WS In Worksheets
            Select Case WS.Name
              Case "Lookups"  ' added names of sheets NOT to clear
                Case Else
                    WS.Select
                    If WS.AutoFilterMode = True Then: ActiveSheet.AutoFilterMode = False
                        For Each NameX In ThisWorkbook.Names
                            If InStr(1, NameX, WS.Name) > 0 Then
                                NameX.Delete
                            End If
                        Next
                    ActiveWindow.FreezePanes = False
            End Select
        Next
    On Error GoTo 0

Open in new window


Try that and let me know
0
 
Shanan212Commented:
Dim nRange As Name , WS as worksheet

 For Each WS In Worksheets    
    For Each nRange In ThisWorkbook.Names 
        If InStr(1, nRange, ws.name) Then 
            nRange.Delete 
        End If 
    Next 
 Next Ws

Open in new window


The above set up will delete all named ranges

Dim nRange As Name , WS as worksheet
 For Each WS In Worksheets    
'in here put an if statement to check for specific worksheets 
'eg: if ws.name = "sheet1" then
    For Each nRange In ThisWorkbook.Names 

        If InStr(1, nRange, ws.name) Then 
            nRange.Delete 
        End If 
    Next 
 Next Ws

Open in new window

0
 
bearblackGlobal Program ManagerAuthor Commented:
Would you mind explaining the use of InStr(1, nRange, ws.name). I am not familiar with this.

Thanks

It still however deleted names on addtional Worksheets
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Shanan212Commented:
Instr function detects if a string/portion of a string exists in another string. In this case, the named range name within the workbook

http://www.techonthenet.com/excel/formulas/instr.php

Did you put in the if statement to make sure it only deletes the specific sheets you want to?
0
 
bearblackGlobal Program ManagerAuthor Commented:
I used the case statement because there are only a few that I don't want to remove out of 22 tabs.
0
 
Shanan212Commented:
Can you post the code you are using? =
0
 
bearblackGlobal Program ManagerAuthor Commented:
Public WS as Worksheet, NameX  as name

' Clear Autofilters and Names
    On Error Resume Next
         For Each WS In Worksheets
            Select Case WS.Name
              Case "Lookups", "YTD Financial Summary", "Info" ' added names of sheets NOT to clear
                Case Else
                    WS.Select
                    If WS.AutoFilterMode = True Then: ActiveSheet.AutoFilterMode = False
                        For Each NameX In ThisWorkbook.Names
                            If InStr(1, NameX, ws.name) Then
                                NameX.Delete
                            End If
                        Next
                    ActiveWindow.FreezePanes = False
            End Select
        Next
    On Error GoTo 0
0
 
bearblackGlobal Program ManagerAuthor Commented:
It didn't work but when I changed:

For Each NameX In ThisWorkbook.Names
 To
For Each NameX In WS '(Public WS as Worksheet)

It then worked -- Thanks for the help and getting me on the right track.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.