Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop thru named ranges (Excel 2000 - 2007)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop thru named ranges (Excel 2000 - 2007)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop thru named ranges (Excel 2000 - 2007)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop thru named ranges (Excel 2000 - 2007)

    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

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop thru named ranges (Excel 2000 - 2007)

    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.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop thru named ranges (Excel 2000 - 2007)

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

    <pre>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
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop thru named ranges (Excel 2000 - 2007)

    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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Loop thru named ranges (Excel 2000 - 2007)

    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").RefersToRan ge
    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").RefersToRan ge.EntireRow.Hidden = True

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop thru named ranges (Excel 2000 - 2007)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •