VBA to clean up named ranges.

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2431
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

VBA to clean up named ranges.

Post by 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
Technical Director
www.infocat.co.uk
User avatar
gtonkin
MVP
Posts: 1216
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: VBA to clean up named ranges.

Post by gtonkin »

HI Steve, another option we have used is Python and OpenPyXL (worksheet.defined_names) to read and remove. Far faster than VBA code.
User avatar
Steve Rowe
Site Admin
Posts: 2431
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: VBA to clean up named ranges.

Post by Steve Rowe »

Interesting, had not encountered this before.
Technical Director
www.infocat.co.uk
Post Reply