[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
ClaudeWalker
Asked:
ClaudeWalker
  • 3
  • 2
2 Solutions
 
Rory ArchibaldCommented:
You need to loop through the names in the Workbook rather than Worksheet, if you want to capture all names.
0
 
Rory ArchibaldCommented:
Eg:
For Each nm In wks.Parent.Names

Open in new window

0
 
zorvek (Kevin Jones)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

Kevin
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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.  

0
 
Rory ArchibaldCommented:
How else do you expect to check for ranges without the sheet name?
0
 
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now