• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

named ranges

Dear experts,

In a spread sheet i have several named ranges, i need to clairfy the following:

1. Is there a feature in excel where i can look at the various name ranges, and in that i can findout the sheet and cell range the name range refersto

2. Can a macro be provided to me where by it will in a new sheet list all the name ranges in column A and then in column B it will list the sheet name it refers to and in column C it will list the cell range.

If there any other special conditions for the name ranges then these can also be referred to in the columns adjacent tot he name range.

Thank you

3 Solutions
Ctrl+G takes you go Goto dialog box where you can see your named ranges.
If you have Excel 2010, you can go to Formula tab, and click on Name Manager. It will open a dialog window like the attached image.

ExcellearnerAuthor Commented:
Hi JPpinto,

thank you for your comment, i have excel 2007.

Kindly advice how to access this information.

For this limited purpose, i woul dprefer a macro to extract this information for me.

Thank you
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

You can use Ctrl+F3 to get to that box.

You can also:
1) insert a new sheet
2) press Alt+F11
3) Ctrl+G
4) Type:
5) Press Alt+F11 again, and see all the names and their ReferTo contents in the column to the right.
In 2007 Excel you have also this Name Manager under the Formulas tab. It will display all of the information that you want.
p.s.: After you type the command on the immediate window, do not forget to press <ENTER> at the end of that line
I also like to run the code below BEFORE you run the method ListNames of the Range object.
reason: invisible names do not get listed in the sheet.

Sub MakeNamesVisible()
On Error GoTo ErrorTrap
Dim wbk         As Workbook
Dim nm          As Name
    With Application
        .DisplayAlerts = False
        .CalculateBeforeSave = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Set wbk = ActiveWorkbook
    For Each nm In wbk.Names
        if not nm.Visible then nm.Visible = True

    With Application
        .DisplayAlerts = True
        .CalculateBeforeSave = True
        .EnableEvents = True
        .Calculation = xlCalculationSemiautomatic
    End With
Exit Sub
Resume Next
End Sub

Open in new window

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

you can get a list of range names and what sheets and cells they refer to by using Alt - i - n - p and then click Paste List. Or click the Formulas Ribbon > Use in Formula > Paste names > Paste List

cheers, teylyn

teylyn, amazing!!
I never knew where the ListNames method of the Range object was triggered from Excel's interface !

Thanks, :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now