Solved

# VBA - Excel Union Method: Setting Dynamic range

Posted on 2010-09-07
903 Views
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
Question by:Shino_skay
• 2
• 2
• 2

LVL 85

Accepted Solution

Rory Archibald earned 250 total points
ID: 33622022
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

LVL 92

Assisted Solution

Patrick Matthews earned 250 total points
ID: 33622058
``````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
``````
0

LVL 92

Expert Comment

ID: 33622060
slow fingers :)
0

Author Comment

ID: 33622126
oh, so you can set rngunion to union itself. That's the secret.... Thanks rorya and pat
0

Author Comment

ID: 33622258
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

LVL 85

Expert Comment

ID: 33622344
Yes, that should work.
0

## Featured Post

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.