VBA - Excel Union Method: Setting Dynamic range


I'm stuck trying to figure out how to use the union method on a dynamic range array.
I have an range array for a number of banks which changes from period over period. Instead of using

Set rngUnion = Union(rngBank(1), rngBank(2), rngBank(3), etc)

is there a way to set the rngUnion to the bounds of the array?

Unless there's a Set Preserve, I can't think of a loop for this. Thanks guys!
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Rory ArchibaldConnect With a Mentor Commented:
For n = Lbound(rngbank) to ubound(rngbank)
if rngunion is nothing then
   set rngunion = rngbank(n)
   set rngunion = union(rngunion, rngbank(n))
End if
next n

for example.
Patrick MatthewsConnect With a Mentor Commented:
How about this:
If UBound(rngBank) = 1 Then
    Set rngUnion = rngBank(1)
    Set rngUnion = Union(rngBank(1), rngBank(2))
    For Counter = 3 To UBound(rngBank)
        Set rngUnion = Union(rngUnion, rngBank(Counter))
End If

Open in new window

Patrick MatthewsCommented:
slow fingers :)
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Shino_skayAuthor Commented:
oh, so you can set rngunion to union itself. That's the secret.... Thanks rorya and pat
Shino_skayAuthor Commented:
quick question guys, is it possible to use the worksheetfunction.rank or application evaluate and rank for a range that's been unioned? I'm getting errors on both attempts and wonder if its possible. Thanks.
Rory ArchibaldCommented:
Yes, that should work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.