Walking through all named ranges in excel VBA in the current active sheet
Hey. I go through all the named ranges found in my active worksheet and then do something with them. However, I used the code below, but it doesn't produce anything. Also, it will be nice if I can loop through the named ranges that contain specific words. For example my named ranges are named data1, data2, data3, etc. I would only like to work on them if they contain the given words.
For Each nm In Activesheets.Names
MsgBox "nm.name"
Next nm
source to share
If you only want to get names from active sheet 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 sheet.
nm.RefersToRange.Parent
will return the worksheet associated with the range.
Then we can get its name using .Name
and compare it with the name ActiveWorksheet.
Here you can see that I have 2 Named Ranges on Sheet4
and 1 onSheet3
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
)
source to share
This macro will traverse all named ranges in your workbook. It takes the comments above into account 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. Usage InStr()
will find "data" anywhere in the name. So if you have data1
, datas1
and myData1
, it will run the "do stuff here" code for all of these. If you just want ranges starting with data
, then change the line InStr()
to:If Left(nm.Name,4) = "data" Then
Edit: you can link this with the line If nm.RefersToRange.Parent.Name = ActiveSheet.Name
suggested by @ user1274820 below. (Just make the operator If
turn on the operator And
).
source to share