Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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.
Written by Allen Wyatt (last updated December 17, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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 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:
https://jkp-ads.com/excel-name-manager.asp
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11787) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Deleting All Names but a Few.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Want to make an entry of the same value into a group of selected cells? It's easy to do with just one small change in how ...
Discover MoreWhen you type information into a cell, Excel tries to figure out what type of information you are entering. If Excel can ...
Discover MoreFew things are as frustrating as trying to delete rows or columns and having Excel tell you that you can't perform the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-03-04 12:10:33
J. Woolley
For related discussion, see https://excelribbon.tips.net/T010338_Getting_Rid_of_Unused_Range_Names.html
2022-12-17 09:38:52
J. Woolley
My Excel Toolbox includes the following dynamic array function to list defined names (named ranges) with workbook, worksheet, or any scope, including names that are normally hidden:
=ListNames([Scope],[SkipHidden],[SkipHeader])
The list includes the following columns: Scope, Name, Visible, Refers To, Value, Comment.
See https://sites.google.com/view/MyExcelToolbox/
In older versions of Excel you can use it with the SpillArray function like this:
=SpillArray(ListNames(...))
See UseSpillArray.pdf at https://drive.google.com/file/d/1HTw_rYNZR2I6dE9C__qFhbd8YtC7rv17/view
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments