bloat from redundant named ranges?

[merlin777]

i have a large workbook i've been modifying for weeks and it has dozens of redundant named ranges.

2 questions:

would they slow the performance of the workbook and therefore better to remove them other than it just being a matter of tidiness?

if i need to remove them is there a way i can check they are definitely unused before i delete them?

 

[TMShucks]

I wouldn't have thought performance would be an issue, particularly if they are static. If they are dynamic named ranges there might be some calculation involved.

But, personally, I'd say it's better to get rid now than in six months when you're wondering what they were all for.

You should be able to use Find (Find and Select icon on the Ribbon). If you type the Named Range name into the Find box and select Find all, you'll get a list of where it is used or a message to say it's not found. Unfortunately, you'd have to do that sheet by sheet ... but you could knock up a macro to loop through all the sheets and check each named range on each one.

You can get a list of all the Named Ranges by going to the Formula ribbon, choosing "Use in Formula", Paste names, Paste List ... best to create a new sheet first as it could be quite long and might overwrite stuff if you're not careful.

 

[merlin777]

thanks for this. although they are big i only have a few sheets so that sounds great.