CODEWITHSUNDEEP
vbaexcel
hend
hend

Reputation: 615

Loop through all named ranges in a Excel Sheet

I need to loop through all named ranges in my sheet.
I am currently doing this:

For Each nm In ActiveWorkbook.Names

The problem is that this sheet has names with global scope and with sheet scope.
This loop only gets the global scope.
I've tried:

For Each nm In Activeworkbook.Sheets(1).Names

But couldn't make it work. This loop also only gets those named ranges with global scope. Ideas?
I know the best solution would be to change the scope of the name, but I can't do it.

Upvotes: 3

Views: 21540

Answers (3)

Jun
Jun

Reputation: 1

Dim Obj as Variant
For Each Obj In ThisWorkbook.Names
    If InStr(Obj.Name, "ExternalData") > 0 Then
        Obj.Delete
    End If
Next Obj

Refining hend's answer, we can use InStr to determine if a string is within the name range's name, which works for both local and global scope name ranges

Upvotes: 0

hend
hend

Reputation: 615

Could make it to work today. To post here, I made my problem much more simple, but this wasn't a good idea.

What I really do is to import some values from a sheet (selected with application.getopenfilename and opened with workbooks.open). So I loop through all names in this "imported" sheet and import the values of those ranges to ranges with the same name in my original sheet.

for each nm in thisworkbook.names
    if left(nm.name, 5) = "campo" then
        'here I make my copy
    end if
next nm

Turns out that when you have a name with sheet scope, nm.name returns something like this:

 nameOfSheet!nameOfField

So I could never get in to that if. To solve my problem I used the following line. Thank you all for trying to help me.

currentName = Mid(nm.Name, InStr(1, nm.Name, "!") + 1)

Upvotes: 7

bilbo_strikes_back
bilbo_strikes_back

Reputation: 591

Dim intCounter As Integer
Dim nmTemp As Name

For intCounter = 1 To ActiveWorkbook.Names.Count
   MsgBox ActiveWorkbook.Names(intCounter)
Next intCounter

Upvotes: 3

Related Questions