Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Deleting All Names but a Few.
by Allen Wyatt
(last updated August 20, 2016)
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 can be 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. You could also use the Name Manager to delete them (press Ctrl+F3), but that can be very time consuming as well.
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 (11787) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Deleting All Names but a Few.
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!
When you are copying a cell from one place to another (perhaps even to a different worksheet), you may not want to copy ...Discover More
When you work with imported or pasted data in an Excel worksheet, you may see some strange looking characters at times. ...Discover More
Defined names can be a great boon when working in a worksheet. Usually names are available throughout an entire workbook, ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.