Leading the way in Microsoft Office Development

Step 1 - Getting a List of Named Ranges

Any worksheet specific named ranges will not be in the list unless they are specific to the worksheet you are pasting the list on.

If you have a workbook that contains a lot of named ranges it can be very useful to be able to get a quick list.

Creates a two column list of the named range and its corresponding reference.

Using the (Insert > Name > Paste) dialog box.

Step 2 - Using the (Insert > Name > Paste) dialog box

This dialog box is very similar to the Define dialog box and will display all the named ranges available on the active worksheet.

An alternative way to display this dialog box is to use the shortcut key (F3).

Any worksheet specific named ranges will not appear in this list unless they are specific to the active worksheet.

Paste List - Pastes the list of named ranges with their cell references to the active cell. Pastes all names defined in your workbook, starting at the active cell.

OK - Selecting a named range and pressing OK will insert the named range into the active cell or formula.

Cancel - Closes this dialog box.

It is possible to get a full list of all the named ranges within a workbook by pressing (Insert > Name > Paste) and select Paste List. Make sure you have enough blank cells below the active cell.

You can easily obtain a list of all the named ranges in a workbook by choosing (Insert > Paste) and selecting Paste List.

Step 3 - Displaying the List

To get a list of all names in the current workbook (including worksheet level names) Select an empty area of the worksheet.

Select cell "B2" and select (Insert > Name > Paste) and select Paste List.

On a worksheet, the list of names is an area two columns wide. The left column lists names; the right column lists the cell references, formulas, or constants to which the names refer.

The list will be displayed in alphabetical order.

This list will not include any worksheet level named ranges for any of the worksheets other than the active worksheet.

You can also create a list of the available names in a workbook. Locate an area with two empty columns on the worksheet (the list will contain two columns — one for the name and one for a description of the name). Select a cell that will be the upper-left corner of the list. In the Paste Name dialog box, click Paste List.

Step 4 - Things to Remember

Make sure you have enough blank cells below the active cell to display all the named ranges. Any cells containing values will be overwritten without prompting.

Only the worksheet level named ranges on the active worksheet are displayed in the (Insert > Name > Paste) dialog box.

Only the worksheet level named ranges on the active worksheet will appear in your pasted list.

If you accidentally overwrite some data you can use (Ctrl + Z) to undo the changes.

(F3) - Displays the (Insert > Name > Paste) dialog box.

You can quickly insert a named range into a formula by pressing F3, selecting the named range and pressing OK.

Any named ranges that have been added with VBA code and have been hidden will not be listed.