8

I am looking to loop through all named ranges found in a activeworksheet and then do something to them. I've used the code below but it does not seem to produce anything. Also, it will be good if I can loop through named ranges that contain certain words. For example, my named ranges are named data1, data2, data3 and so on. I would only like to work on them if they contain the word data.

For Each nm In Activesheets.Names
    MsgBox "nm.name"
Next nm
Christopher Oezbek
  • 23,994
  • 6
  • 61
  • 85
user7729135
  • 399
  • 1
  • 3
  • 11
  • 2
    Try just `msgbox nm.Name`. The message box, as you have it, is literally showing "nm.name", yeah? – BruceWayne Apr 05 '17 at 18:37
  • 1
    You may want 'ActiveSheet' and not 'ActiveSheets' as well. – Brandon Barney Apr 05 '17 at 18:41
  • You need to use `ActiveWorkbook.Names` and drop the quotes as others have said – user1274820 Apr 05 '17 at 18:58
  • I tried 'Activesheet' and remove the quotes. they don't work. and also there are other named ranges which I don't want to touch as well. hence the need to use the current sheet and not the whole workbook – user7729135 Apr 05 '17 at 19:01
  • Please see my edit - I think it will answer your question. – user1274820 Apr 05 '17 at 19:01
  • your edit gives an error of run-time error '1004' – user7729135 Apr 05 '17 at 19:04
  • @user7729135 I tested it without issue and included a demo gif in my post. What version of excel are you using? Are you putting the code into a module? Let me know if there's anything I can do to help – user1274820 Apr 05 '17 at 19:43
  • @user1274820 i tried to copy and post that whole thing in. it still gives me the error. i am using excel 2016 on windows. tried on mac as well – user7729135 Apr 05 '17 at 19:54
  • @user7729135 can u open a blank workbook, paste the code in, and run it - confirm that it works without the other code? I imagine you have something else messing it up - I can't get it to give me a `1004` error using option explicit (which would mean you need `Dim nm`) or anything else. Can you post any code that is running alongside it? – user1274820 Apr 05 '17 at 20:06

3 Answers3

16

If you only want to get names from the active worksheet, use this:

Sub Test()
For Each nm In ActiveWorkbook.Names
    If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then MsgBox nm.Name
Next nm
End Sub

^ This code will only return named ranges that refer to ranges on the active worksheet.

nm.RefersToRange.Parent will return the worksheet associated with the range.

We can then retrieve its name using .Name and compare it to the ActiveWorksheet name.

Here you can see I have 2 Named Ranges on Sheet4 and 1 on Sheet3

When I run this code, it only returns MyName1 and MyName2 - it does not include MyName3 as it is not on the active sheet.

Ranges

This macro will only return the ones on my ActiveSheet (Sheet4)

Names

user1274820
  • 7,786
  • 3
  • 37
  • 74
  • 1
    If you have auto filters on, you might want to add something like `And Right(nm.name, 15) <> "_FilterDatabase"` to your `If` clause, because it seems Excel defines the filtered columns this way. (What would be a better way of discerning between names you defined and potentially other things like these that might be used "behind the scenes"?) – Ben Philipp Aug 10 '21 at 13:26
7

This macro will loop through all named ranges in your workbook. It takes the comments above into consideration and shows how you can do things with specific ranges.

Sub nameLoop()
Dim nm
Dim wb As Workbook
Set wb = ActiveWorkbook
For Each nm In wb.Names
    If InStr(1, nm.Name, "data") Then
        ' Do stuff with the named range.
        Debug.Print nm.Name
    End If
Next nm
End Sub

Note, using InStr() will find "data" anywhere in the name. So if you have data1, datas1, and myData1, it'll run the "do stuff here" code for all of those. If you just want ranges starting with data, then change the InStr() line to: If Left(nm.Name,4) = "data" Then

Edit: You can couple this with the If nm.RefersToRange.Parent.Name = ActiveSheet.Name line suggested by @user1274820 below. (Just make the If statement include an And operator).

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
0

Just a little addition to Ben Philipp's comment: _FilterDatabase names have their .Visible property set to False.

If OP haven't added invisible names via VBA before, they could just check the .Visible property instead of comparing strings.

Guy Bukzi Montag
  • 1
  • 1