Loop thru named ranges (Excel 2000 – 2007)

Topic

New Reply

I am trying to loop thru all named ranges on a sheet and based on the value of a boolean flag hide or show the roll of cells of the named range. Hiding or showing them is no problem but finding a collection of named ranges I am having trouble with. Is there a collection of range names for a given sheet? Thank you

Reply | Quote

Replies

Both the Workbook and the Worksheet objects have a property called Names.
If you define a name using Insert | Name | Define, you usually create a global name, so it will be part of the workbook’s Names collection. Only if you make it a local name by prefixing the name with the worksheet name (for example MySheet!MyName), it’ll be part of the worksheet’s Names collection.

Type Names somewhere in the Visual Basic Editor and press F1 to get help.

Reply | Quote

Hans below is the code I am using. What this gives me is the cells I named but not the name of the range of the cell which is what I am looking for. What I am trying to do is based on that there is a given nmuber of range names with predefined prefixes. A loop is used to go thru all of the named ranges and those with the predifined prefixed will have something performed on them.

Dim iNumNames As Integer
iNumNames = Application.ActiveWorkbook.Names.Count

Dim iLoop As Integer
Dim sRName As String

For iLoop = 0 To iNumNames

sRName = Application.ActiveWorkbook.Names(iLoop)
MsgBox sRName

Next iLoop

Reply | Quote

In the first place, the loop should start at 1:

For iLoop = 1 To iNumNames

If you want to retrieve the name, you should say so explicitly:

sRName = ActiveWorkbook.Names(iLoop).Name

Reply | Quote

Thank you Hans. I have a mental block on the collections in Office for some reason. When I see the word “names” I think it will return an array of names rather than think in terms of collection of Names objects. Thank you again. I also think in terms of zero base.

Reply | Quote

To loop through the names on a worksheet, you can do this, which does both local and global ones:

Option Explicit

Sub NamesLoop()
    Dim oNm As Name
    For Each oNm In ThisWorkbook.Names
        If oNm.RefersToRange.Parent.Name = ActiveSheet.Name Then
            MsgBox oNm.Name & ":" & oNm.RefersTo
        End If
    Next
End Sub

Reply | Quote

Thank you Pieterse. Trying to create a Range object from knowing the range name is proving to be a problem. From the range object I will hide the row or rows the named range is on.

Reply | Quote

Perhaps I don’t completely understand, but to get the range object from a name can be done with code like:

Dim rng As Range
Set rng = ActiveWorkbook.Names(“NameOfInterest”).RefersToRange
rng.EntireRow.Hidden = True

Replace “NameOfInterest” with the name you want to hide.

It can also be shortened to (if you don’t want to do anything with the range object):
ActiveWorkbook.Names(“NameOfInterest”).RefersToRange.EntireRow.Hidden = True

Steve

Reply | Quote

Thank you Steve. Thank all you guys for the help.

Reply | Quote