Solved

vba macro name ranges

Posted on 2013-05-20
5
467 Views
Last Modified: 2013-05-20
Dear experts,

I have a file, which has got about 200 named ranges. The file is not confidential/proprietary infomation.

Can I please request a macro which name the named range in column A, then range to which the named range pertains. In the macro you provide other details also to be included, which I may not know are important.

The macro will do a great in understanding methology and will be greatly appreciated.

Thank you
0
Comment
Question by:Excellearner
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39181234
Hi,


I appreciate that English may not be your first language, but I am struggling to understand your requirements (within the first sentence in this paragraph):
Can I please request a macro which name the named range in column A, then range to which the named range pertains. In the macro you provide other details also to be included, which I may not know are important.

Please can you either provide an example of what you require, or re-word your requirements?

Thanks.

BFN,

fp.
0
 

Author Comment

by:Excellearner
ID: 39181409
Hi fanpages,

I need a macro which could do the following:

1. List the several 'name ranges'
2. Provide details like sheet name and cell range to which the name pertains to.


If the expert believes that a name range could include other details (not requested be me in either 1 or 2), then the macro could list the same

The output shall be provided by the macro by creating a new sheet in the file.

Sorry for my negligence.

Thank you
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39181490
:)

No need to apologise.

Just to help with testing any relevant Visual Basic for Applications code written for this purpose, do you have an example of your workbook that you could attach to this thread?

The code could then be applied directly to that & re-posted for your convenience.


You may not wish to release sensitive information so, if applicable, please remove the relevant data from the workbook &/or change specific details to hide the true content.

However, the example code I have provided a hyperlink to below may well meet your requirements:

[ http://www.mrexcel.com/forum/excel-questions/44907-list-out-named-ranges-visual-basic-applications.html#post205980 ]

Option Explicit
Sub Rprt()
Dim nm As Name, n As Long, y As Range, z As Worksheet
Application.ScreenUpdating = False
Set z = ActiveSheet
n = 2
With z
    .[a1:g65536].ClearContents
    .[a1:D1] = [{"Name","Sheet Name","Starting Range","Ending Range"}]
    For Each nm In ActiveWorkbook.Names
        .Cells(n, 1) = nm.Name
        .Cells(n, 2) = Range(nm).Parent.Name
        .Cells(n, 3) = nm.RefersToRange.Address(False, False)
        n = n + 1
    Next nm
End With

Set y = z.Range("c2:c" & z.[c65536].End(xlUp).Row)
y.TextToColumns Destination:=z.[C2], DataType:=xlDelimited, _
    OtherChar:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))
[a:d].EntireColumn.AutoFit

Application.ScreenUpdating = True
End Sub

Open in new window


(Many other examples exist if you simply use Named Range Excel as Google.com search criteria)
0
 

Author Comment

by:Excellearner
ID: 39182240
Hi fanpages,

Thank you for the comment, but I am getting an error and unable to run the code referred by you.


I found the below code from ID: 20473902.

Sub ListNamedRanges()
Dim wb As Workbook, ws As Worksheet, sh As Worksheet, nm As Name

Set wb = ActiveWorkbook
Set ws = wb.Worksheets.Add

ws.Range("A1:C1").Value = Array("Name", "Refers To", "Sheet/Workbook")

For Each nm In wb.Names
    With ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
        .Value = nm.Name
        .Offset(0, 1) = "'" & nm.RefersTo
        .Offset(0, 2) = wb.Name
    End With
Next nm

For Each sh In wb.Worksheets
    For Each nm In sh.Names
        With ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
            .Value = nm.Name
            .Offset(0, 1) = "'" & nm.RefersTo
            .Offset(0, 2) = sh.Name
        End With
    Next nm
Next sh

ws.UsedRange.AutoFormat Format:=xlRangeAutoFormatSimple

End Sub

The code provided me with the required output.

Thank you
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39182398
Hi again,

I am glad you found a working solution in any respect.

What error message did you see with the code hosted at MrExcel.com?

BFN,

fp.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now