VBA union of two ranges
 

I'm trying to define a range, consisting of two separate ranges on two
different worksheets within the same workbook. From the help section of
Excel 2000, I got the following sample, but when the ranges are on separate
sheets, I get an error. Is there some way of accomplishing this?


Sub MultipleRange()
Dim r1, r2, myMultipleRange As Range
Set r1 = Sheets("Sheet1").Range("A1:B2")
Set r2 = Sheets("Sheet2").Range("C3:D4") <== I changed the sample to
show a different sheet
Set myMultipleRange = Union(r1, r2)

For Each c In Range("myMultipleRange")
If (Not IsEmpty(c)) And (Not c.Value = "SYMBOL") Then _
QuoteStr = QuoteStr & "+" & c.Value
Next
End Sub

Thanks for any guidance,

Dick Sanders

VBA union of two ranges
 

Dick,

A Range can contain cells only from one worksheet. You can't
combine ranges on two worksheets in to a single range.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"s" wrote in message
news:3%U0b.9588$cj1.9571@fed1read06...
I'm trying to define a range, consisting of two separate ranges
on two
different worksheets within the same workbook. From the help
section of
Excel 2000, I got the following sample, but when the ranges are
on separate
sheets, I get an error. Is there some way of accomplishing
this?


Sub MultipleRange()
Dim r1, r2, myMultipleRange As Range
Set r1 = Sheets("Sheet1").Range("A1:B2")
Set r2 = Sheets("Sheet2").Range("C3:D4") <== I changed
the sample to
show a different sheet
Set myMultipleRange = Union(r1, r2)

For Each c In Range("myMultipleRange")
If (Not IsEmpty(c)) And (Not c.Value = "SYMBOL") Then _
QuoteStr = QuoteStr & "+" & c.Value
Next
End Sub

Thanks for any guidance,

Dick Sanders