Iterate through Ranges but not find certain ranges

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
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
For Each nm In wks.Parent.Names

Open in new window

Rory ArchibaldCommented:
You need to loop through the names in the Workbook rather than Worksheet, if you want to capture all names.
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

ClaudeWalkerAuthor Commented:
For Each nm In wks.Parent.Names

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

Rory ArchibaldCommented:
How else do you expect to check for ranges without the sheet name?
ClaudeWalkerAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.