[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • 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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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