Solved

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

Posted on 2013-11-12
8
302 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

624 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