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 :)
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Unhide very hidden sheets with password 22 45
Msgbox tickler 13 30
sumifs excel 2013 3 16
Saving excel document in lotus script 4 13
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

821 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