VBA union of two ranges

[s]

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("C34") <== 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

 

[Chip Pearson]

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 (e-mail address removed)