Excel.Tips.Net ExcelTips (Menu Interface)

Deleting All Names but a Few

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
        End If
    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.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

jayes    22 Aug 2016, 05:50
it does nothing on my excel i do not see other names deleted
Luciana    11 Oct 2012, 10:29
Good morning.
I tried the macro above, and it works very well to delete all names but a few... However the names that remain on the file have always # REF as reference and before I had no error on the references ... Is there a way to keep the name and the reference to the name ?
Thank you very much

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.