Hidden named ranges

[pam53154]

I am having problems with numerous hidden named ranges being introduced to files when a tab containing those names are moved into a new file. The named ranges don't have a functional purpose and originated with files we received from a service provider. The named ranges bog down the file performance. I downloaded a name manager tool that is effective (but slow) in removing the names. However, I really need a way to prevent users from copying an "infected" tab into a new workbook in the first place so this stops spreading. Any ideas? I thought I had this wiped out previously and asked users to be mindful, but now the ranges are back anyways and I think it will continue to be that way until an actual mechanism to prevent it is put in place.

 

[James006]

Hi,

Which name manager tool do you use ?
The one written by Jan Karel Pieterse is truly great!!!

In addition, you could have a macro deleting all these named ranges...

HTH

​

 

[pbornemeier]

If there are no desired named ranges in your workbook then you can add the following code to the ThisWorkbook codepage that will delete all Named Ranges whenever any worksheet is selected:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Sub DeleteNamedRanges()
    
        Dim nm As Name
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
        
        For Each nm In ActiveWorkbook.Names
            nm.Delete
        Next
    
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
    
    End Sub

End Sub

 

[pam53154]

Thanks for both comments. I am using the JKP tool. It does work great. However, as an example--the file I am "cleaning" right now has 8,353 names I need to delete. So, it takes a very long time, and there are many files like this. Unfortunately I cannot use a macro to delete all named ranges since some of the hidden ranges are needed for the file to function. For example, if I delete the hidden range called "__IntlFixup which seems to be in all files, the file will no longer open. There are also hidden named ranges for filters, print areas and data connections that cannot be deleted without significant pain to re-establish.

 

[pam53154]

I'm considering VBA code that would function something like - before the workbook is saved, check for the presence of --named range--<named range="">. If found, do not save with message box of "--named range--<named range=""> found in this workbook, please do not save until this is removed." I know enough about coding to think that is possible, but I'm not sure exactly how to do it. Thoughts?</named></named>

 

[pbornemeier]

This code will delete only those names in the workbook that refer to it the activeworksheet only if that worksheet name is not listed in the code.

Add this code to a standard module.

Option Explicit

Sub KillActiveWorkSheetNamesIfWorksheetIsNotExempted()

    Dim nm As Name
    Dim sName As String
    Dim bDelete As Boolean
    Dim lDeleteCount As Long
    
    If ActiveSheet.Type <> -4167 Then GoTo End_Sub 'Must be a worksheet
    
    Select Case ActiveSheet.Name
    Case "Sheet1", "Sheet3"     'Names of all worksheets that should not be deleted must be listed here in the following format:  "Sheet1", "Sheet3"
        msgbox "Names cannot be automatically deleted from this worksheet.", , "Protected Worksheet"
    Case Else
        sName = ActiveSheet.Name
    
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
    
        For Each nm In ActiveWorkbook.Names
            bDelete = False
            'Debug.Print nm.Name, nm.RefersTo
            If InStr(nm.RefersTo, "='" & sName & "'!") > 0 Then bDelete = True
            If InStr(nm.RefersTo, "=" & sName & "!") > 0 Then bDelete = True
            If bDelete Then
                nm.Delete
                lDeleteCount = lDeleteCount + 1
            End If
        Next
        
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        
        If lDeleteCount > 0 Then
            msgbox lDeleteCount & " names deleted that referred to worksheet " & sName
        End If
        
    End Select

End_Sub:

End Sub