Solved

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

Posted on 2013-11-12
8
279 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

776 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