Solved

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

Posted on 2013-11-12
8
295 Views
Last Modified: 2013-11-12
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
0
Comment
Question by:bearblack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 39642053
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
 
LVL 2

Author Comment

by:bearblack
ID: 39642236
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
 
LVL 13

Expert Comment

by:Shanan212
ID: 39642261
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 2

Author Comment

by:bearblack
ID: 39642317
I used the case statement because there are only a few that I don't want to remove out of 22 tabs.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 39642342
Can you post the code you are using? =
0
 
LVL 2

Author Comment

by:bearblack
ID: 39642356
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
 
LVL 13

Accepted Solution

by:
Shanan212 earned 500 total points
ID: 39642389
         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
 
LVL 2

Author Closing Comment

by:bearblack
ID: 39643167
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question