Using 2 Variable Ranges at the same time

Noki

New Member
Joined
Apr 16, 2008
Messages
48
Hi All,
I haven't needed to post here for quite some time but I'm stuck again.

Basically I have a selection Ranges stored as Variables, in the example below I've simplified it to just 2, Bob and Bill. I would like to glue the 2 Ranges together based on some other input (there are 9 in total, sometimes I will want to use all 9 ranges, other times just 1, and sometimes a random selection somewhere in between) Once I get the correct selection of ranges I can use them to make a graph update.

Example: Bob & Bill makes Bobill!

Code:
Sub RangeTest()
Dim bob As Range
Dim bill As Range
Dim bobill As Object 'tried this one
Dim bobill As Range 'and this one, same error

Set bob = ActiveWorkbook.ActiveSheet.Range("A1")
Set bill = ActiveWorkbook.ActiveSheet.Range("B2")

bobill = bob & bill 'tried this one
Set bobill = bob & bill 'and this one, same error

End Sub

Unfortunately gluing them together using '&' did not work and I can't think what the syntax should be to make this work, I've tried searching google and these forums but I don't think I'm looking for the right thing any help would be appreciated.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Then 2 minutes later I find the answer, typical:

Set bobill = Union(bob, bill)

Thanks everyone!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top