How do I delete just the Workbook scoped instance of a range name, and not its local instance of the same range name?

In Excel2007, we have several large spreadsheets that use worksheet scoped range names.  However for various reasons we can not control - some users also have some old global scoped range names in the same book which happen to have the same names.  We are trying to clean out the books of all these old global names.  As there are dozens of copies of these files each containing hundreds of these old global names, going into the name manager to physically select the global (workbook) level name for each range to delete is not going to be practical.

The specific problem occurs when that same name exists as both a workbook level name and a worksheet scoped level name.  We want to delete the workbook level name but retain the local worksheet level name which is correct.

I have confirmed that i can use the ValidWorkbookParameter property of a range name to determine if there is a global (workbook) level instance of that name but when it then does go to delete the name, it actually deletes the local scoped instance too.

Does anyone have advice for how to limit the delete to JUST the global (workbook) scoped instance of the name?  Any help would be appreciated.
Dim nName As Name

For Each nName In ThisWorkbook.Names
  If nName.ValidWorkbookParameter = False Then
    MsgBox nName.Name  'just used for confirmation test purposes
    nName.Delete   
  End If
Next nName

End Sub

Open in new window

shellysuelllAsked:
Who is Participating?
 
krishnakrkcConnect With a Mentor Commented:
Hi,

Again,

replace

If InStr(1, .Names(lngLoop).Name, NameName) Then

with

If .Names(lngLoop).Name Like "*" & NameName Then

Kris
0
 
krishnakrkcCommented:
Hi,

Try

Sub kTest()
   
    Dim strName     As String
    Dim Pos         As Long
    Dim Nm          As Name
   
    With ThisWorkbook
        For Each Nm In .Names
            strName = Nm.Name
            Pos = InStr(1, strName, "!")
            If Pos Then
                If ISNAMEGLOBALLYEXISTS(strName) Then
                    .Names(CStr(Mid$(strName, Pos + 1))).Delete
                End If
            End If
        Next
    End With
   
End Sub
Private Function ISNAMEGLOBALLYEXISTS(ByVal NameToCheck As String) As Boolean
   
    Dim strName As String
   
    On Error Resume Next
    strName = Split(NameToCheck, "!")(1)
    ISNAMEGLOBALLYEXISTS = (ThisWorkbook.Names(CStr(NameToCheck)).Name = ThisWorkbook.Names(CStr(strName)).Name)
   
End Function

Kris
0
 
krishnakrkcCommented:

Ignore my post. It won't  work.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
shellysuelllAuthor Commented:
Krishnakrkc, thanks for the attempt!  Any other ideas?  
This item just has me completely stumped.
0
 
krishnakrkcCommented:
Hi,

Download this add-in. This might help you.

http://www.jkp-ads.com/officemarketplacenm-en.asp

Kris
0
 
shellysuelllAuthor Commented:
Thanks krishnakrkc for resonding again, I appreciate it.

Yes I have used that addin previously for similar purposes and it is very helpful for making the manual selection/deletion of the extra global range names a bit easier.

In our case however we were trying to actually add this deletion logic into an existing addin that they already use to access and work with these files.  So that we could 1) not have to have the users install yet another addin and 2) that we could then just directly integrate an automated clean up into their existing meubar that they use for working with these files to make SURE that this clean up occurs.  

So is there not a known way for us to do this deletion directly from VBA - without having to go through another addin's interface?  

Again - many thanks for your continued help!
0
 
Rory ArchibaldCommented:
Is this happening for all your named ranges or only ones that are named locally to the first worksheet? There is a longstanding Excel quirk whereby any local named range on the first sheet in a workbook overrules any workbook level name, even in code. A simple solution is to insert a blank sheet at the start of the workbook, perform your deletions, and then remove the blank worksheet.
0
 
krishnakrkcCommented:
Hi,


Try

Option Explicit
Public Enum NameScope
    xlWorkbook = 0
    xlWorksheet = 1
End Enum
Sub DeleteNamedRanges(ByRef Wbk As Workbook, ScopeLevel As NameScope)
   
    '// Developed by    : Krishnakumar @ ExcelFox.com
   
    Dim lngLoop     As Long
    Dim lngIndex    As Long
    Dim strName     As String
    Dim wksTemp     As Worksheet
    Dim lngSU       As Long
    Dim lngCalc     As Long
    Dim lngEE       As Long
    Dim lngDA       As Long
   
   
    With Application
        lngSU = .ScreenUpdating
        lngCalc = .Calculation
        lngDA = .DisplayAlerts
        lngEE = .EnableEvents
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .EnableEvents = False
    End With
   
    Set wksTemp = Wbk.Worksheets.Add
   
    With Wbk
        For lngLoop = .Names.Count To 1 Step -1
            If ScopeLevel = xlWorksheet Then
                If TypeOf .Names(lngLoop).Parent Is Worksheet Then
                    strName = Split(.Names(lngLoop).Name, "!")(1)
                    If GLOBALLYEXISTS(Wbk, strName) Then
                        .Names(lngLoop).Delete
                    End If
                End If
            ElseIf ScopeLevel = xlWorkbook Then
                If TypeOf .Names(lngLoop).Parent Is Workbook Then
                    strName = .Names(lngLoop).Name
                    If LOCALLYEXISTS(Wbk, strName) Then
                        .Names(lngLoop).Delete
                    End If
                End If
            End If
        Next
    End With
   
    wksTemp.Delete
   
    With Application
        .ScreenUpdating = lngSU
        .Calculation = lngCalc
        .DisplayAlerts = lngDA
        .EnableEvents = lngEE
    End With

End Sub
Private Function GLOBALLYEXISTS(ByRef Wbk As Workbook, ByVal NameName As String) As Boolean
   
    Dim lngLoop     As Long
    Dim lngSU       As Long
   
    With Application
        lngSU = .ScreenUpdating
        .ScreenUpdating = False
    End With
   
    With Wbk
        For lngLoop = .Names.Count To 1 Step -1
            If .Names(lngLoop).Name = NameName Then
                If TypeOf .Names(lngLoop).Parent Is Workbook Then
                    GLOBALLYEXISTS = True
                    GoTo Xit
                End If
            End If
        Next
    End With
Xit:
    Application.ScreenUpdating = lngSU
   
End Function
Private Function LOCALLYEXISTS(ByRef Wbk As Workbook, ByVal NameName As String) As Boolean
   
    Dim lngLoop     As Long
    Dim lngSU       As Long
   
    With Application
        lngSU = .ScreenUpdating
        .ScreenUpdating = False
    End With
   
    With Wbk
        For lngLoop = .Names.Count To 1 Step -1
            If .Names(lngLoop).Name = NameName Then
                If TypeOf .Names(lngLoop).Parent Is Worksheet Then
                    LOCALLYEXISTS = True
                    GoTo Xit
                End If
            End If
        Next
    End With
Xit:
    Application.ScreenUpdating = lngSU
   
End Function

and call like

Sub kTest()
       
    'Delete Global Names where both the scope exists (Local and Global)
    DeleteNamedRanges ThisWorkbook, xlWorkbook
   
End Sub

HTH

0
 
krishnakrkcCommented:

@ rory,

Sorry! didn't read your post until I post this solution.

I tried with deleting the names with its index, but every time it deletes the local names, not the workbook level names.

Kris  
0
 
Rory ArchibaldCommented:
Seems like overkill to me - I would think this would be fine to just delete global names:
Sub testing()
   delglobal ActiveWorkbook
End Sub
Sub delglobal(wbk As Workbook)
   Dim nme As Name
   Dim wks As Worksheet
   Set wks = wbk.Worksheets.Add(before:=wbk.Sheets(1))
   wks.Activate
   For Each nme In wbk.Names
      If InStr(nme.Name, "!") = 0 Then nme.Delete
   Next nme
   Application.DisplayAlerts = False
   wks.Delete
   Application.DisplayAlerts = True
End Sub

Open in new window

0
 
krishnakrkcCommented:

I think the OP needs to delete those names which have both the scope (Local and Global). I might be wrong

0
 
shellysuelllAuthor Commented:
rorya  - thanks for your response.
Here is some more infomration that may help

It is happening with names on a variety of sheets (not just limited to first sheet situations).  
Based on continued evaluation of these books yesterday, the common thread seems to just be when you have a formula in book A that has a formula using a local scoped range name in book B (regardless of which page it is on).  And Book B has both an local worksheet scoped and an old global scoped version of that name AND that global scoped name has a #REF value on it as it is pointing to some other external book that is current not open and hence the link is unresolved.

As background:  Here is why it is important that we find a way to get rid of those old Global scoped names, but we need to keep the local names in place.

When you open Book A before Book B - we found a quirk with excel where it automatically deletes the local range name that the formulas in A are trying to point to.  Hence leaving B with just the Global name, which then actually triggers excel to change the syntax of the formulas in book A to now be pointed at the global name in B, which is not the right location.

I have found reference to this quirk on a few other sites, and their workaround has been to basically always open book B before book A (which is not going to work in our case as there is data flow meeting this criteria going both ways between book A and B, so reordering the opening fixes it for one book, but then triggers it the other way for the other).

Since that seems to be a bug on the excel side, we are trying to prevent the fact pattern causing it.  Which seems to lead us to
1 - needing to delete the global instance of these names before they start opening the files together (which triggers the behavior described above where Excel automatically deletes the local scoped names being pointed to by the first book opened)
2 - or taking an alternate take of pushing changes to their formulas in the 2 books to make them coordinate based to remove any reference to these range names.
0
 
shellysuelllAuthor Commented:
krishnakrkc - thanks for the suggestion.
I will be in a meeting for the next few hrs, but will give it a try as soon as i get out.
Thanks!
0
 
shellysuelllAuthor Commented:
krishnakrkc
I had a chance to test your suggestion and upon completion both names still exist (the global workbook scoped and the local worksheet scoped).

I have stripped down the files to a small sample with just a single original page plus some blank sheets; and have deleted a few dozen total range names to simplify things for troubleshooting purposes.  So this is a super simple scenario just to try to test the deletion logic.

Still no logic - but definite Kudos and Thanks for the suggestion!


StrippedDownCopy.xlsm
0
 
krishnakrkcCommented:
Hi,

Couple of changes.


Replace this line from Function LOCALLYEXISTS

 If .Names(lngLoop).Name = NameName Then

with

If InStr(1, .Names(lngLoop).Name, NameName) Then

and also replace in the main sub

If TypeOf .Names(lngLoop).Parent Is Workbook Then
                    strName =  .Names(lngLoop).Name

with

If TypeOf .Names(lngLoop).Parent Is Workbook Then
                    strName = "!" & .Names(lngLoop).Name


Also put this code in the standard module, rather than sheet module.

Kris
0
 
shellysuelllAuthor Commented:
Krishnakrkc
YEAH IT WORKS!

Thanks a ton for your persistence in helping to find a solution to this.
I super appreciate it - you rock!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.