Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Deleting All Names but a Few.
Do you routinely work with worksheets that contain dozens (or hundreds) of named cells, and most of those names are unnecessary? Cleaning up the names is a huge task, but getting rid of the ones you don't need can make your workbook smaller and more efficient. The problem is, how do you get rid of a lot of unnecessary names all at once? You can certainly delete them one at a time, but such a process quickly gets tiresome.
One possible solution is to simply create a new workbook and copy the cells from the old workbook to the new one. Highlight the cells in the old workbook, use Ctrl+C to copy them, then paste them into worksheets in the new workbook. This will copy almost everything from the old workbook—formulas, formatting, etc. It does not bring copy over print settings or range names. The only task then remaining is to redefine the few names you want in the new workbook.
If you prefer to work with the old workbook (the one with all the names), it is best to create a macro that will do the name deletion for you. You need a macro that will allow you to delete all the names except those you want to keep. The following is a simple approach that accomplishes this task:
Sub DeleteSomeNames() Dim vKeep Dim nm As Name Dim x As Integer Dim AWF As WorksheetFunction 'Add Names to keep here vKeep = Array("Name1", "Name2") Set AWF = Application.WorksheetFunction For Each nm In ActiveWorkbook.Names x = 0 On Error Resume Next x = AWF.Match(nm.Name, vKeep, 0) On Error GoTo 0 If x = 0 Then nm.Delete End If Next Set AWF = Nothing End Sub
Before using the macro, modify the line that creates the vKeep array. Simply enter the names you want to keep within the array, each name surrounded by quotes and separated by commas. (In the example shown here, the names "Name1" and "Name2" will be kept.) The macro loops through all the names in the workbook and uses the Match function to see if the name is one in the array. If it is not, then it is deleted.
If you prefer to use a third-party solution to managing the names in your workbook, a great choice is the Name Manager add-in, written by Jan Karel Pieterse. You can find more information on the add-in here:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2419) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Deleting All Names but a Few.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!