Solved

vba macro name ranges

Posted on 2013-05-20
5
469 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

773 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