Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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, :-)

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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