Solved

vba macro name ranges

Posted on 2013-05-20
5
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

724 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