VBA to clean up named ranges.

[Steve Rowe]

We occasionally come across workbooks with a large number of named ranges and this impacts for the performance of PAfE.

The most obvious indication that you are suffering from this is just moving from cell to cell with the arrow keys has a lag but it will also hurt the calculation performance. If the problem is severe then the file on disc will be much larger than you think it should be too.

VAB to Unhide all named ranges.
If you use this and then the Name Manager will not display then the book had so many names in it that the Name Manager is crashing out (I'm assuming some kind of data type issue). If you are in this situation then even the second piece of VBA to delete the names will take an age to run. If you are in this extreme situation then you will need to unzip the Excel file on disc and delete the named ranges from the underlying xml in notepad++.

Code: Select all

Sub UnhideAllNames()

    Dim nm As Name
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    For Each nm In ActiveWorkbook.Names

        nm.Visible = True

    Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Delete all named ranges, obviously this may break working formula, use with caution.

Code: Select all

Sub DeleteAllNames()
    Dim nm As Name
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each nm In ActiveWorkbook.Names

        nm.Delete

    Next
    On Error GoTo 0
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[gtonkin]

HI Steve, another option we have used is Python and OpenPyXL (worksheet.defined_names) to read and remove. Far faster than VBA code.

[Steve Rowe]

Interesting, had not encountered this before.