Iterate through Ranges but not find certain ranges

Posted on 2011-04-26
Last Modified: 2012-05-11
So I am iterating through all the ranges in a worksheet.  However, if a range is a part of a workbook then it is not showing up.  When I initially add a range to a sheet it does not assign it to the worksheet.

Example:  If I copy a worksheet over I get

RangeName     'SheetName' in the range Define section of Excel

if I create the range on that sheet I get

RangeName only

Then when I iterate through it only picks up the Ranges on the worksheet with RangeName     'SheetName'

How can I either add a range to get that RangeName     'SheetName' format in the Name->Define section or how can I check for ranges without the 'SheetName' element.
For Each nm In wks.Names
        'delim = GetPathSection(nm.Name, 2, "!")
        Debug.Print nm.Name
        If delim <> "" Then
            missing = missing & delim & "; "
        End If
    Next nm

Open in new window

Range define section
Question by:ClaudeWalker
    LVL 85

    Expert Comment

    by:Rory Archibald
    You need to loop through the names in the Workbook rather than Worksheet, if you want to capture all names.
    LVL 85

    Accepted Solution

    For Each nm In wks.Parent.Names

    Open in new window

    LVL 81

    Assisted Solution

    by:zorvek (Kevin Jones)
    When you define a name Excel decides whether it is a workbook level name or a sheet level name. This usually happens without any user knowledge. By default all names are workbook level names. Excel makes a name a worksheet level name when one of two things happen. Either you are adding a second name to the workbook that already exists on another sheet. Or you preface the name with the worksheet name when defining it:

       'My Sheet'!MyRange


    Author Comment

    For Each nm In wks.Parent.Names

    If I do this it will go through all of the worksheet ranges in the workbook.  

    LVL 85

    Expert Comment

    by:Rory Archibald
    How else do you expect to check for ranges without the sheet name?

    Author Comment

    Good Point.  I suppose I'll leave it up to the end user to check.  They are okay at using excel.

    Thanks for the tips,
    JOe K.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now