Looping through all named ranges in excel VBA in current active sheet
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
When I run this code, it only returns
MyName2 - it does not include
MyName3 as it is not on the active sheet.
This macro will only return the ones on my
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
InStr() will find "data" anywhere in the name. So if you have
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
Admin about 2 months