Removing unused named ranges

[Tkeller]

I have inherited a massive file and have eliminated several worksheets. Now, I would like to clean up the workbook. Of the hundereds of named ranges, only a fraction of them are now in use. Is there an easy way, to determine which ones are no longer in use and delete them?

Much thanks

 

[Richard Schollar]

Hi

You will find Name Manager useful for this (and any other tasks with Names):

http://www.decisionmodels.com/Downloads/NameManager_40.zip

 

[Jonmo1]

you can use Excel's Find Feature....

edit - find

enter the range name

under options, have it lookin WORKBOOK

click FIND ALL

if none are found then there are no formulas referring to it....

then do the same in VBA to see if there is any references to it in VBA code..

open VBA window - (right click excel icon in top left corner - view code)
Edit - Find

Select "CURRENT PROJECT"

that will find any references to that range name in the VBA code.


of coarse, depending on the names, it may take some logical guesswork on your part to figure out which ones are Named Range References, and which ones are just words meant to stand for something else...

 

[Datsmart]

This earlier post has code to remove named ranges that have #REF errors.
http://www.mrexcel.com/board2/viewtopic.php?t=245577&postdays=0&postorder=asc&&start=0

 

[ktab]

I think that any sheet deleted also deletes his child names. So your problem is to find which names in remaining sheets are not used. In that case namemanager as richard suggested will be extremely useful. But to rebuild the book is the best way so avoid leftovers and cleanup the mess. Also take a look at VBA code cleaner add-in; if someone could help on this.. i do not remember where i've downloaded this from.

 

[Oaktree]

(snip)you can use Excel's Find Feature....

edit - find

enter the range name

under options, have it lookin WORKBOOK

click FIND ALL

if none are found then there are no formulas referring to it....

(snip)

Be a little careful with this one, jonmo...just because there isn't a formula referencing the named range doesn't mean that it's not used. As one example, if you set up data validation to allow a named range as the list source, its use in data validation will not be picked up by Excel Find.

 

[Richard Schollar]

I think that any sheet deleted also deletes his child names. So your problem is to find which names in remaining sheets are not used. In that case namemanager as richard suggested will be extremely useful. But to rebuild the book is the best way so avoid leftovers and cleanup the mess. Also take a look at VBA code cleaner add-in; if someone could help on this.. i do not remember where i've downloaded this from.

Would that be this one Kostas?

http://www.appspro.com/Utilities/CodeCleaner.htm

 

[ktab]

Exactly. Great tool.
Thank you Richard

 

[Tkeller]

Thanks

That tool is awesome.

Wish I had known about this before.