• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

vba macro name ranges

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
Excellearner
Asked:
Excellearner
  • 3
  • 2
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
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
 
ExcellearnerAuthor Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
:)

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
 
ExcellearnerAuthor Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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