We help IT Professionals succeed at work.

Iterate through Ranges but not find certain ranges

Medium Priority
300 Views
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You need to loop through the names in the Workbook rather than Worksheet, if you want to capture all names.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
For Each nm In wks.Parent.Names

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

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
How else do you expect to check for ranges without the sheet name?

Author

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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.