Delete Range Names From A Specific Sheet

jstevens
GoldLounger
Posts: 2640
Joined: 26 Jan 2010, 16:31
Location: Southern California

Delete Range Names From A Specific Sheet

Post by 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
Regards,
John

User avatar
HansV
Administrator
Posts: 79699
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete Range Names From A Specific Sheet

Post by 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.
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2640
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Delete Range Names From A Specific Sheet

Post by jstevens »

Hans,

I got it to work.

Thank you,
John :cheers:
Regards,
John

User avatar
HansV
Administrator
Posts: 79699
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete Range Names From A Specific Sheet

Post by HansV »

Good for you!
Best wishes,
Hans

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 658
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Delete Range Names From A Specific Sheet

Post by 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
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: Delete Range Names From A Specific Sheet

Post by 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
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

jstevens
GoldLounger
Posts: 2640
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Delete Range Names From A Specific Sheet

Post by 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
Regards,
John