Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Delete Named Ranges Specific To 1 Worksheet

  1. #1
    Join Date
    4th November 2006
    Posts
    76

    Delete Named Ranges Specific To 1 Worksheet

    Hi,
    I have a workbook with named ranges in different worksheets.
    I want to delete the names of these ranges only in Sheet1.

    If I write:

    VB:
    For Each Name In ActiveWorkbook.Names 
        Name.Delete 
    Next Name 
    
    
    it of course deletes everything.

    But if I write:

    VB:
    For Each Name In Sheets("Sheet1").Names 
        Name.Delete 
    Next Name 
    
    
    or even:

    VB:
    Sheets("Sheet1").Activate 
    For Each Name In ActiveSheet.Names 
        Name.Delete 
    Next Name 
    
    
    it deletes nothing at all. And it gives no error message.

    So, what do I write to delete all names in Sheet1?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Delete Names In A Specific Worksheet

    Hi,

    VB:
    Sub kTest() 
        Dim sName As Name 
         
        For Each sName In ThisWorkbook.Names 
            If InStr(1, sName, "Sheet1") Then 
                sName.Delete 
            End If 
        Next 
    End Sub 
    
    
    HTH

  3. #3
    Join Date
    4th November 2006
    Posts
    76

    Re: Delete Names In A Specific Worksheet

    Works fine, thanx!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    13th May 2014
    Posts
    1

    Re: Delete Named Ranges Specific To 1 Worksheet

    The solution provided almost 6 years ago still works great. Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th September 2011
    Posts
    9

    Re: Delete Names In A Specific Worksheet

    Quote Originally Posted by Krishnakumar View Post
    Hi,

    VB:
    Sub kTest() 
        Dim sName As Name 
         
        For Each sName In ThisWorkbook.Names 
            If InStr(1, sName, "Sheet1") Then 
                sName.Delete 
            End If 
        Next 
    End Sub 
    
    
    HTH
    Thanks for the starting point, but this didn't quite work for me as not all of my formulas had the sheet name in them. I found and tailored this solution to delete exclusively on "scope". I hope this is useful to someone.

    VB:
    Sub Global_Local_names() 
        Dim n As Name 
        Dim sht As String 
         
        sht = "Sheet1" 
         
        For Each n In ThisWorkbook.Names 
            If TypeOf n.Parent Is Worksheet Then 
                If n.Parent.Name = sht Then 
                    n.Delete 
                End If 
            End If 
        Next 
    End Sub 
    
    
    Modified from here:
    http://stackoverflow.com/questions/8...-to-a-workbook

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd April 2007
    Posts
    3,886

    Re: Delete Named Ranges Specific To 1 Worksheet

    Try

    VB:
    Dim oneName As Name 
     
    For Each oneName In ThisWorkbook.Names 
        On Error Resume Next 
        If oneName.RefersToRange.Parent.Name = "Sheet1" Then oneName.Delete 
        On Error Goto 0 
    Next oneName 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Delete All Non Valid Named Ranges
    By ehoory in forum Excel General
    Replies: 4
    Last Post: June 26th, 2008, 10:59
  2. Write Names Of Named Ranges Of Specific Sheet Into Array
    By nzcarrick in forum Excel General
    Replies: 3
    Last Post: May 22nd, 2008, 20:30
  3. Delete All Named Ranges With Exceptions
    By Manels1111 in forum Excel General
    Replies: 2
    Last Post: October 11th, 2006, 10:04
  4. Named Ranges in specific cells
    By Timbo in forum Excel General
    Replies: 4
    Last Post: October 13th, 2004, 00:46
  5. Replies: 6
    Last Post: September 5th, 2003, 03:01

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno