Looping through all named ranges in excel VBA in current active sheet

Question

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

Solution 1

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.

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

Solution 2

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).

Comments

[Ben Philipp]

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"?)