Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Delete Named Ranges Specific To 1 Worksheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:

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

    But if I write:

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

    Code:
        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?

  • #2
    Re: Delete Names In A Specific Worksheet

    Hi,

    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
    Kris

    ExcelFox

    Comment


    • #3
      Re: Delete Names In A Specific Worksheet

      Works fine, thanx!

      Comment


      • #4
        Re: Delete Named Ranges Specific To 1 Worksheet

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

        Comment


        • #5
          Re: Delete Names In A Specific Worksheet

          Originally posted by Krishnakumar View Post
          Hi,

          Code:
          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.

          Code:
          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

          Comment


          • #6
            Re: Delete Named Ranges Specific To 1 Worksheet

            Try

            Code:
            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

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X