List out named ranges with VBA

[EconSean]

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

 

[brett_x]

a little late to the party ...

ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
Selection.ListNames

Late, Simple, works. Thank you

 

[acontrario]

Even later to the party...

Adding to NateO post & to others, not being an expert, but having found his contribution most helpful as I had the same question.

When using a ListNames method referenced above, in my workbook tested, I got 101 names
When using NateO method, I got 128 names, so 27 out
Where did this difference come from?

3 names come from names that are defined at worksheet level, so not in the ListNames (see below how to identify them)
2 names come from functions, not in the ListNames (see below how to identify them)
8 names contain !_FilterDatabase ; I don't know when and why I contributed creating these
4 names contain !_ftn ; I don't know the meaning - relates to an imported worksheet
10 names contain !Print_ (Print_Area or Print_Titles)
So, it reconciles the gap.

NateO great sub can be easily enhanced if needed to get a more complete list.
As suggested above, adding a
On Error Resume Next
before filling the cells will help as some Names items attributes contain nothing, so trigger an error when putting their value in variables.

one can get also (adding columns) more Name items attributes:

- nm.Index giving the Item number in the Names list
- "'" & nm.RefersTo giving the full range address with a quote upfront otherwise it will get the underlying values
- nm.Category as this will contain "All" for functions, otherwise Nothing (empty)
- nm.MacroType as this will contain 1 for functions, otherwise -4142

for functions, RefersTo contains =#Name? and Sheet Name Parent is empty, so triggers an error necessitating the error trap above

How do we get the Scope referred to as in Name Manager (workbook or worksheet)?
The only way I found was to test if the Name did not contain the worksheet name followed by ! before the Name

For workbook Names, this is obviously not necessary.

There are other properties visible in the watch window but I found no use for them.

 

[snellij]

Hello

Using Excel 2016/Office 365

Just on the off chance – would you know of a way that I canquickly identify all of the ordinary ranges within a spreadsheet?

I need to be able to identify them and then change them allto named ranges. (a secondary task)

Currently I am manually searching for the colon symbol,which every range contains between the two cell references, one at a time. Thisis very time consuming so I’m looking for a faster, more accurate solution.

Many thanks in anticipation.

John

 

[Kmenon]

Howdy Sean, use a counter instead of a numeric loop, something like:

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

While running the above code, it gives a run-time error "1004". Method 'Range' of object '_Global' failed. Any help please ?