Solved

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

Posted on 2013-11-12
8
272 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
highlight duplicate entry 16 31
Help with Excel formula 6 38
Applying Background Image to All Sheets in Excel 1 17
Excel 2016 loop through 6 0
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now