Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Finding Unused Names

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: Finding Unused Names.

Richard has a workbook that he's been using for a while, and it has quite a few names in it (named ranges, named formulas, etc.). He wonders if there is an easy way to find names that are not used at all, as he'd like to get rid of those names.

There is no built-in way to get rid of these unused names. You can, however, create a macro that will do the trick for you. This is most easily done by using the Find method to figure out which names have references that can be "found." If the reference cannot be found, then the name is not in use.

Sub RidOfNames()
    Dim myName As Name
    Dim fdMsg As String

    On Error Resume Next
    fdMsg = ""
    For Each myName In Names
        If Cells.Find(What:=myName.Name, _
          After:=ActiveCell, _
          LookIn:=xlFormulas, _
          LookAt:=xlPart, _
          SearchOrder:=xlByRows, _
          SearchDirection:=xlNext, _
          MatchCase:=False, _
          SearchFormat:=False).Activate = False Then
            fdMsg = fdMsg & myName.Name & vbCr
            ActiveWorkbook.Names(myName.Name).Delete
        End If
    Next myName
    If fdMsg = "" Then
        MsgBox "No unused names found in the workbook"
    Else
        MsgBox "Names Deleted:" & vbCr & fdMsg
    End If
End Sub

The macro steps through all the elements of the Names collection and does a search for each name. If the name cannot be found, then the name is deleted. When the macro is completed, it displays a message box that lists the names that were removed from the workbook.

If you would rather not create your own macro, you can opt to use a free add-in by Jan Karel Pieterse. The add-in, called Name Manager, allows you to (guess what?) manage names better than you can do with native Excel. One of the functions it provides is the ability to get rid of names that are no longer needed. You can find the add-in here:

http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3312) 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: Finding Unused Names.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Leave your own comment:

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

Comments for this tip:

DK    20 Sep 2016, 11:26
Code looks correct, but for some reason it deletes names that are actually being used by cells O_o
Michael (Micky) Avidan    14 Nov 2014, 10:47
Beside trying with 2-3 names - it searches the entire workbook.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Steve    13 Nov 2014, 16:36
Does this search the entire workbook or only the active sheet? Thanks.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

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.