Delete Range Names From A Specific Sheet

[jstevens]

I am having a challenge deleting all the range names associated with a specific sheet. I am aware of the "Name Manager" written by Jan Karel and have used it with success.

Here is an example of the code I am using: trying to produce a list in Sheet1 for those range names in Source2. I figured if I can at least identify the range names then deleting them would be a snap.

Code: Select all

Sub DeleteNames_In_Source2()
    Dim Nm As Name
    oCounter = 0
    For Each Nm In Sheets("Source2").Names
        Sheets("Sheet1").Range("A" & oCounter + 1) = Nm
        Application.StatusBar = "Deleting Range Named: " & Nm
        oCounter = oCounter + 1
'        Nm.Delete   'This line 
    Next
    Application.StatusBar = False
    MsgBox "Finished"
End Sub

I'm not seeing all the range names associated with Source2.

Thanks for taking a look,
John

[HansV]

Even though a name refers to a range on Source2, it may still be a global name.
Sheets("Source2").Names is the collection of names that are local to Source2, it doesn't include global names.

You can use Jan Karel's Name Manager to check which names are global and which are local.

To find all names that refer to Source2, you'd have to loop through all global names and all local names for all sheets, and parse their RefersTo property to check whether they refer to a range on Source2.

[jstevens]

Hans,

I got it to work.

Thank you,
John

[HansV]

Good for you!

[Jan Karel Pieterse]

Just so you can see what is involved in getting referred sheets form a range name:

Code: Select all

Function FindReferencedSheets(sRefersto As String) As Variant
    Dim oSheet As Worksheet
    Dim sMatches() As String
    Dim lCount As Long
    Dim lTest As Long
    Const sValidChars As String = "abcdefghijklmnopqrstuvwxyz0123456789_.?\áàâäãåæçðéèêëƒíìîïñóòôöõøœšßþúùûüýÿž"

    '
    ' first element (0) is always returned empty
    '
    ReDim sMatches(1)
    lCount = 0
    For Each oSheet In ActiveWorkbook.Worksheets
        'JKP: april 14 2005. Changed search for sheetnames to make sure
        'JKP: "aSheet1" is not found when Sheet1 is the sheet we're looking for!
        ' crw 18/8/2005 shorter code
        lTest = InStr(sRefersto, oSheet.Name & "!") + InStr(sRefersto, "'" & oSheet.Name & "'!") _
                + InStr(sRefersto, oSheet.Name & ":") + InStr(sRefersto, "'" & oSheet.Name & "':") _
                + InStr(sRefersto, ":" & oSheet.Name) + InStr(sRefersto, ":'" & oSheet.Name & "'")
        If lTest > 0 Then
            '
            ' check that preceding character is NOT a valid char or '
            '
            If InStr("'" & sValidChars, Mid(sRefersto, lTest - 1, 1)) = 0 Then
                lCount = lCount + 1
                ReDim Preserve sMatches(lCount)
                sMatches(lCount) = oSheet.Name
            End If
        End If
    Next
    If lCount >= 1 Then
        FindReferencedSheets = sMatches
    Else
        '
        ' crw 18/8/2005 fix for non-array variant
        '
        FindReferencedSheets = Array("", "")
    End If
GoExit:
End Function

[steveh]

Hi John

If you are lazy (like me) you can use the Range facility in the http://www.asap-utilities.com/download- ... lities.php

Range.jpg

[jstevens]

Jan Karel,

I can see where it gets quite involved. My solution was to look at the range name's formula and where Source2 was found, delete the name.

Code: Select all

    For Each Nm In ActiveWorkbook.Names

        If InStr(1, Nm, "Source2") Then
            Sheets("Sheet1").Range("A" & oCounter + 1) = Nm.Name
            Sheets("Sheet1").Range("B" & oCounter + 1) = Nm
                   oCounter = oCounter + 1
            Application.StatusBar = "Deleting Range Named: " & Nm.Name
                Nm.Delete
        End If

    Next

Thank you for the example,
John