Solved

VBA - Excel Union Method: Setting Dynamic range

Posted on 2010-09-07
6
920 Views
Last Modified: 2013-11-10
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
Comment
Question by:Shino_skay
  • 2
  • 2
  • 2
6 Comments
 
LVL 85

Accepted Solution

by:
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

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

Expert Comment

by:Patrick Matthews
ID: 33622060
slow fingers :)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

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

Author Comment

by:Shino_skay
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

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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 …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

861 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