Names | Delete names in cells

 This page in 2003
Top  Previous  Next

The Delete Names in Cells command provides a way of rapidly deleting a quantity of names that you don’t want any more or have made by accident.

Why would you want to use it?

Excel offers two ways to define names in a workbook.

•The built-in Excel Formulas | Defined names | Define name command allows you to add names to a workbook, and to delete them; but it only allows you to do so one name at a time.
•The built-in Excel Formulas | Defined Names | Create from selection command allows you to add a whole block of names to a workbook, many at a time; but it offers no facilities for deleting them.

Operis finds the second method the most convenient way to specify the names in a workbook.  It makes a model self-documenting because it leaves an indication of the names that have been defined next to the values that are being referred to; and it allows lots of names to be defined in a single action.

But there is a dangerous aspect to Formulas | Defined Names | Create from selection: if you use this command by mistake, or mis-select the area that is meant to contain the names you are creating, you can add a great quantity of unintended names to the workbook.

Under such circumstances OAK Development | Names | Other | Delete command can come to the rescue.  Select the cells containing the labels that match the names you want to delete, select this command, and the unwanted names will all disappear.

How to use it

To use this command, follow these steps:

1.Select a range containing labels that match the names you wish to delete;
2.Select OAK Development | Names | Other | Delete;
3.A warning dialog is displayed, where you must press ‘OK’ to continue; remember that this command has no Undo facility.

A given name may appear several times in a workbook, once at book level (a global name) and once on individual worksheets (a local name).  In these circumstances OAK is designed to give the same result as would arise if the names were typed, one by one, into the Excel Formulas | Defined names | Define name command and the ‘Delete’ button pressed.  In other words:

•the Delete Names command will never delete local names not defined on the active worksheet;
•if the text in a selected cell corresponds to both a global name and a local name defined on another worksheet, the global name is deleted;
•if the text in a selected cell corresponds to both a global name and a local name defined on the active worksheet, that local name is deleted.  Note that if the name appears twice in the list, the global name will be deleted as well.

What you could do if you didn't have OAK

You don't need OAK to get rid of unwanted names.  You can delete them through Excel, using its Formulas | Defined names | Define name command.  However, you will need to handle each name individually, and to pick out the names to be removed from a potentially long alphabetic list.

Wrinkles

This command does not work when applied to protected worksheets.  You must manually unprotect them first.

This command does not follow the One-Cell rule.  This is to prevent all the names that happen to be mentioned on a worksheet from being deleted whenever the command is selected with only one cell selected.

If you want to delete all the names mentioned on a worksheet, select the whole sheet (Ctrl+A does this quickly) before using the command.