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: