select multiple named ranges with macro

[Ted M H]

I have a macro that looks at the active cell and determines the named
ranges that it is a part of. It can be one named range or many. I
store the names in an array, one name per element in the array.
After populating the array with the names, I want to select all of the
named ranges that have been stored in the array.
I can do this easily like this:

Range(“mg_benefits, mg_FY11,mg_fy12â€).select

but when I try to do the same thing from my array it
doesn’t work.
My array is: Dim vSelections(1 to 100) as Variant

I fill the first three elements with the names above. I’ve tried

Range("vSelections(1), vSelections(2),vSelection(3)").Select

and

Range(vSelections(1), vSelections(2) ,vSelection(3)).Select

But these don’t work. I think I’m missing something simple, but I
can’t figure it out. Any suggestions?

 

[Jacob Skaria]

Try the below

Dim varName(1 To 3)
varName(1) = "Name1"
varName(2) = "Name2"
varName(3) = "Name3"
strname = Join(varName, ",")
Range(strname).Select

If this post helps click Yes

 

[Jacob Skaria]

OR try

Range(vSelections(1) & "," & vSelections(2) & "," & vSelection(3)).Select

If this post helps click Yes

 

[Dave Peterson]

Maybe you could drop the array of names and just build a union of ranges:

Option Explicit
Sub testme()
Dim nm As Name
Dim TestRng As Range
Dim myCell As Range
Dim myRng As Range

Set myCell = ActiveCell

For Each nm In ActiveWorkbook.Names
Set TestRng = Nothing
On Error Resume Next
Set TestRng = nm.RefersToRange
On Error GoTo 0

If TestRng Is Nothing Then
'do nothing
Else
If TestRng.Parent.Name <> myCell.Parent.Name Then
'not on the same worksheet, do nothing
Else
If Intersect(TestRng, myCell) Is Nothing Then
'cell not in the range, do nothing
Else
If myRng Is Nothing Then
Set myRng = TestRng
Else
Set myRng = Union(myRng, TestRng)
End If
End If
End If
End If
Next nm

If myRng Is Nothing Then
MsgBox "no names contain this cell"
Else
MsgBox myRng.Address(external:=True) 'just for testing
Application.Goto reference:=myRng, scroll:=False
End If

End Sub

============
But if you had that array, you could use something like:

Sub testme2()

Dim iCtr As Long
Dim myArr As Variant
Dim myRng As Range

'this replaces your code that determines the array of names
myArr = Array("test1", "test2", "test3", "test5")

Set myRng = ActiveWorkbook.Names(myArr(LBound(myArr))).RefersToRange

For iCtr = LBound(myArr) + 1 To UBound(myArr)
Set myRng = Union(myRng, _
ActiveWorkbook.Names(myArr(iCtr)).RefersToRange)
Next iCtr

MsgBox myRng.Address(external:=True) 'just for testing
Application.Goto reference:=myRng, scroll:=False

End Sub

============
Before you spend too much time creating your own name utility, you may want to
look at Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It has this feature built in--and lots of other good stuff, too.

 

[Dave Peterson]

There could be a bug in that second routine -- depending on how you used those
names.

Sub testme2()

Dim iCtr As Long
Dim myArr As Variant
Dim myRng As Range
Dim myCell As Range

Set myCell = ActiveCell

'this replaces your code that determines the array of names
myArr = Array("test1", "test2", "test3", "test5")

Set myRng = mycell.parent.range(myarr(lbound(myarr)))

For iCtr = LBound(myArr) + 1 To UBound(myArr)
Set myRng = Union(myRng, myCell.Parent.Range(myArr(iCtr)))
Next iCtr

MsgBox myRng.Address(external:=True) 'just for testing
Application.Goto reference:=myRng, scroll:=False

End Sub

mycell.parent is the worksheet that owns the cell in question.

If you used workbook level names and worksheet level names, then the
..referstorange may not be on the correct worksheet.