Looping through range names

[Craig]

I saw the following from an earlier post:

Dim nm As Name
For Each nm In ThisWorkbook.Worksheets(5).Names
MsgBox nm.Name
Next nm

This is what I would like to do - loop through all the ranges on a given
page, and then do something.

However, the above does not seem to work. It only show the "Print Area"
range, then the for next statement quits.

Any thoughts as to why it doesn't show ALL the ranges on Worksheet(5),
because there are about 50 of them on my spreadsheet.

Thanks for all help.

 

[Rowan]

Named ranges are part of the workbooks names collection so try:

Dim nm As Name
For Each nm In ThisWorkbook.Names
MsgBox nm.Name
Next nm

Hope this helps
Rowan

 

[Norman Jones]

Hi Craig,

This is what I would like to do - loop through all the ranges on a given
page, and then do something.

The procedure you show loops through the *named* ranges only.

I think you would need to provide more detail about your intentions, but, by
way of demonstration, the following loops through each discrete region on
"Sheet1" of the active worksheet and lists the region address in the
intermediate window:

'====================>>
Public Sub Tester()
Dim Rng As Range
Dim RngA As Range, RngB As Range
Dim RngBig As Range
Dim Ar As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Sheet1")
Set Rng = SH.UsedRange

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

On Error Resume Next
Set RngA = Rng.SpecialCells(xlCellTypeConstants)
Set RngB = Rng.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not RngA Is Nothing Then Set RngBig = RngA

If Not RngB Is Nothing Then
If Not RngBig Is Nothing Then
Set RngBig = Union(RngB, RngBig)
Else
Set RngBig = RngB
End If
End If

If Not RngBig Is Nothing Then
For Each Ar In RngBig.Areas
'Do something with the area, e.g.:
Debug.Print Ar.Address
Next Ar
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<====================

 

[Craig]

Thanks for the ideas...
--
Craig

Hi Craig,


The procedure you show loops through the *named* ranges only.

I think you would need to provide more detail about your intentions, but, by
way of demonstration, the following loops through each discrete region on
"Sheet1" of the active worksheet and lists the region address in the
intermediate window:

'====================>>
Public Sub Tester()
Dim Rng As Range
Dim RngA As Range, RngB As Range
Dim RngBig As Range
Dim Ar As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Sheet1")
Set Rng = SH.UsedRange

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

On Error Resume Next
Set RngA = Rng.SpecialCells(xlCellTypeConstants)
Set RngB = Rng.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not RngA Is Nothing Then Set RngBig = RngA

If Not RngB Is Nothing Then
If Not RngBig Is Nothing Then
Set RngBig = Union(RngB, RngBig)
Else
Set RngBig = RngB
End If
End If

If Not RngBig Is Nothing Then
For Each Ar In RngBig.Areas
'Do something with the area, e.g.:
Debug.Print Ar.Address
Next Ar
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<====================

 

[Norman Jones]

Hi Craig,

I think that Rowan correctly interpreted your query whereas I responded to a
different, unasked question.

Adding to Rowan's response, a workbook may contain workbook level (global
names) and/or worksheet level (local) names.

The workbook names collection returns all names in the workbook whilst the
worksheet names collection returns only local names relating to the
specified worksheet. Worksheet level names are prefixed with the sheet name
and an exclamation mark, e.g.:

Sheet2!MyTotal

The procedure you ran interrogated the worksheet's name collection and it
therefore returned the single existing sheet level name.

Rowan's procedure interrogates the workbook names collection and should,
therefore return all names in the workbook, including any sheet level names.

If you wish to return only workbook names that relate to a specifuc sheet,
you could try something like:

Sub Tester02A()

Dim nm As Name
Dim SH As Worksheet
Set SH = ActiveWorkbook.Sheets("Sheet2") '<<==== CHANGE

For Each nm In ActiveWorkbook.Names
If Range(nm.RefersTo).Parent.Name = SH.Name Then
Debug.Print nm.Name, _
nm.RefersTo, _
Range(nm.RefersTo).Parent.Name
End If
Next nm
End Sub