Solved

VBA - Excel Union Method: Setting Dynamic range

Posted on 2010-09-07
6
908 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vb.net dynamic formulas in runtime 11 62
Consolidate xl 2010 worksheets with text 2 24
Why doesn't duplicate values work on this spreadsheet? 6 35
Most Consistent Performer 4 21
A short article about a problem I had getting the GPS LocationListener working.
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 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

15 Experts available now in Live!

Get 1:1 Help Now