Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 963
  • Last Modified:

VBA - Excel Union Method: Setting Dynamic range

Hi,

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!
0
Shino_skay
Asked:
Shino_skay
  • 2
  • 2
  • 2
2 Solutions
 
Rory ArchibaldCommented:
For n = Lbound(rngbank) to ubound(rngbank)
if rngunion is nothing then
   set rngunion = rngbank(n)
else
   set rngunion = union(rngunion, rngbank(n))
End if
next n

for example.
0
 
Patrick MatthewsCommented:
How about this:
If UBound(rngBank) = 1 Then
    Set rngUnion = rngBank(1)
Else 
    Set rngUnion = Union(rngBank(1), rngBank(2))
    For Counter = 3 To UBound(rngBank)
        Set rngUnion = Union(rngUnion, rngBank(Counter))
    Next
End If

Open in new window

0
 
Patrick MatthewsCommented:
slow fingers :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Shino_skayAuthor Commented:
oh, so you can set rngunion to union itself. That's the secret.... Thanks rorya and pat
0
 
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.
0
 
Rory ArchibaldCommented:
Yes, that should work.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now