Business Tools Blog

Names can be like an infection in shared files - after you delete them, they come back.  And many of the names are broken … like the one shown below.

PC magazine published the following macro to delete names:

Sub NoNames()
For Each n In ActiveWorkbook.Names
n.Delete
Next n
End Sub

Unfortunately this didn’t work for me, and I received a debug error.

I searched and found a free Add-In called Name Manager at the Excel MVP page - written by Jan Karel Pieterse.

NameManager.zip v3.2 (10 November 2004, 572k, 26723 downloads) - Updated to Build 530

If you are in need of a utility to manage defined names in your workbooks, this one is a must-have. List all names in your active workbook. Filter them using 14 filters, e.g. “With external references”, “With errors”, Hidden, Visible. Show just names that contain a substring. Show just names unused in worksheet cells. Edit them in a simple dialog or make a list, edit the list and update all names in one go. Delete, hide, unhide selected names with a single mouse click. Created in collaboration with Charles Williams, www.decisionmodels.com and Matthew Henson (mhenson@mac.com) who ensured the utility works on Mac Excel too.  Version 3.2 of the Name Manager in now available.  Changes since version 3.1 include: sizable form (including minimise/maximise buttons), moving of splits, sorting of names and RefersTo formulas, new add name dialog, new filter: active sheet, shortcut key (default control-shift-n) to start Name Manager. Mac users, please note: version 3.2 has some problems in the Mac environment that we are still trying to solve. For your convenience, Name Manager 2.3 is still available.

I downloaded the NameManager.zip Add-In and can now quickly and easily remove any names.

The Add-In is shown below.  Highlight the names that you want to delete, then click the “X”.  Instead of highlighting one name at a time, you can now delete all names with one click!

Caution - don’t delete all names.  I would stick to deleting the names with errors and names with external references that you don’t have on your computer.  Also, save a backup before deleting (just in case).