asked on

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

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

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.

jppinto
Capture.JPG

excel learner

ASKER

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

SOLUTION

FernandoFernandes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

In 2007 Excel you have also this Name Manager under the Formulas tab. It will display all of the information that you want.

FernandoFernandes

p.s.: After you type the command on the immediate window, do not forget to press <ENTER> at the end of that line

FernandoFernandes

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
        VBA.DoEvents
    Next

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

Open in new window

SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FernandoFernandes

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

Thanks, :-)