VBA - Excel Union Method: Setting Dynamic range

Posted on 2010-09-07
Last Modified: 2013-11-10

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!
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)
   set rngunion = union(rngunion, rngbank(n))
End if
next n

for example.
LVL 92

Assisted Solution

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

LVL 92

Expert Comment

by:Patrick Matthews
ID: 33622060
slow fingers :)
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.


Author Comment

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

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.
LVL 85

Expert Comment

by:Rory Archibald
ID: 33622344
Yes, that should work.

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now