If you’ve ever had to work with spreadsheets that you inherited from other people, chances are they were riddled with obsolete range names that do not point to valid addresses anymore. Getting rid of those one by one by pressing the delete button is tedious and you may accidentally delete a name too many in the process. Tracking range names down however is not too difficult using a bit of visual basic code.

Listing your range names

The first step is to be able to list your range names. This will allow you to inspect the names and do with them as you please later. The following code will generate such a list for you:

VBA Code to List Range Names

It will first open up a new worksheet in order not too mess anything up. Then it loops through all defined names to lists valid adresses and mark invalid ones as such.

Deleting the obsolete range names

Deleting the obsolete / invalid names is a variation on the above code. We now loop backward through the names collection because we do not want to mess up our loop sequence as we delete names:

By trying to store the adress we test for errors and if we find one we delete the name. After running the code (via the <ALT>F8 menu) all names with reference errors will be deleted so only meaningful range names remain.

Happy Hunting!

Leave a Reply

Time limit is exhausted. Please reload the CAPTCHA.