List out named ranges with VBA

EconSean

EconSean

Board Regular
Joined
Apr 21, 2002
Messages
129
Greetings all,

I have a file with a number of named ranges. I would like to have a routine that lists out all of the named ranges (throughout the file, not just on one page) and display all of these names on a sheet.

I have come up with this loop, but it doesn't work exactly right just yet.

Basically, it gets to the last name in the workbook and then fills in 100 cells with the range. I arbitrarily picked 100; I really would like to loop through all named ranges in the file, whether there are 10, 100, or 1000 ranges.

I would like each row to be a unique name. Is there a way to list the name AND the range that the name refers to?

Thanks in advance.

Kind Regards,

Sean


Public Sub ShowNames()

Dim Nm As Name
Dim i As Long

For Each Nm In ActiveWorkbook.Names
For i = 1 To 100
Range("A1").Offset(i, 0).Value = Nm
Next i
Next Nm

End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Change this

For Each Nm In ActiveWorkbook.Names
For i = 1 To 100
Range("A1").Offset(i, 0).Value = Nm
Next i
Next Nm

to this

For Each Nm In ActiveWorkbook.Names
i = i + 1
Range("A1").Offset(i, 0).Value = Nm
Next Nm
 
Upvote 0
Howdy Sean, use a counter instead of a numeric loop, something like:

Code: 
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

Hope this helps.

Edit: Extra Var
 
Upvote 0
You guys never cease to amaze me. Both methods worked, although in this instance Nate's hit the nail right on the head.

Can I ask, where have you guys learned all of this? I have worked with Excel for quite a while, but the amount of material that I don't know that I see discussed on this site astounds me.

Thanks again.

Regards,

Sean
 
Upvote 0
NateO said:
Howdy Sean, use a counter instead of a numeric loop, something like:

Code: 
        .Cells(n, 2) = Range(nm).Parent.Name
Click to expand...

I found that that line caused an error for any name entered via the insert->name->define dialogue box that didn't link to a range (examples: =3 , =hello world {entered without the '=' signs} ). To clarify, any such named constant caused a '1004' run-time error.

I needed to adjust it to

Code: 
        On error resume next
        .Cells(n, 2) = Range(nm).Parent.Name
 
Upvote 0
This is exactly what I came here to find! Thank you all so much for all of your amazing knowledge and for sharing it with the rest of us!!
 
Upvote 0
Some years later, but Microsoft anticipated this need. Ribbon versions of excel :

  1. Select the cell where you want the list to start. Since a name list can occupy a good deal of space, you may want to select a cell in a blank worksheet.
  2. Display the Formulas tab of the ribbon.
  3. In the Defined Names group, click the Use In Formula tool. (This tool is not available if there are no named ranges in your workbook.) Excel displays a list of options.
  4. Choose Paste Names from the submenu. Excel displays the Paste Name dialog box.
  5. Click on Paste List. The two-column list of names and their ranges is inserted.
Source : by Allen Wyatt
 
Upvote 0
a little late to the party ...

Code: 
ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
Selection.ListNames
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Delete Named Ranges That Referring to Another Workbook
Replies
4
Views
932
kevin9999
K
S
  • Question
How to change scope (Workbook to Sheet) of Formula Name after duplicated with Sheet?
Replies
8
Views
661
SunnySchindler
S
K
  • Question
Delete Named Range in a specific Worksheet in the current Workbook
Replies
4
Views
391
My Aswer Is This
My Aswer Is This
I
  • Question
VBA to create sheets from a template based off a list in the master sheet not working when protected
Replies
4
Views
658
Ibis2653
I
D
  • Question
VBA Loop Through Named Ranges
Replies
1
Views
108
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,214,585
Messages
6,120,394
Members
448,957
Latest member
Hat4Life

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top