Yes... I don't understand what the with statement is for. I'm obviously
not an experienced excel programmer. All of what I'm doing occurs with
one sheet.

This is what seems to work for me:

Set MyNew = Union(Range(Cells(Rw, 1), Cells(Rw, 3)), Range(Cells(Rw, 7),
Cells(Rw, 9)))

John

Dave Peterson wrote:
If you removed the dots, then you also removed the with statement. You didn't
need to do that and shouldn't have done that. I can't think of a time when it's
better to use unqualified ranges.

And I'm confused with your explanation. First 3 cells plus 6-10, then not cells
4-6. What happens with column F????

Dim myRow as long
dim myRng as range
myrow = 12 'whatever

with activesheet 'here it is again!
set myrng = union(.cells(myrow,1).resize(1,3), _
.cells(myrow,6).resize(1,5))
msgbox myrng.address
'or
set myrng = union(.cells(myrow,1).resize(1,3), _
.cells(myrow,7).resize(1,4))
msgbox myrng.address
end with

John wrote:
I had to take the periods out of this to get it through debug.

What I',looking for is to have a range that is the first 3 cells in a
row plus cells 6 through 10 in the same row. or put another way. I want
the range to include cells 1 to 10 in a row but not cells 4 through 6.
And I want the row to be variable.

John

Dave Peterson wrote:
But not quite that simple <vbg:


With ActiveSheet 'I like to qualify my ranges.
Set My_Multiple_Range _
= Union(.Cells(X, Y), _
.Range(.Cells(X + 2, Y + 2), .Cells(X + 7, Y)), _
.Cells(X + 7, Y + 7))
End With

or

With ActiveSheet 'I like to qualify my ranges, still.
Set My_Multiple_Range _
= Union(.Cells(X, Y), _
.Cells(X + 2, Y + 2).Offset(0, -2).Resize(6, 3), _
.Cells(X + 7, Y + 7))
End With


Barb Reinhardt wrote:
Then you want
Set My_multiple_Range =
Union(Cells(x,y),cells(x+2,y+2),Cells(x+7,y),cells (x+7,Y+7))

It's not that hard.
--
HTH,
Barb Reinhardt

"John" wrote:

I wasn't clear

I want something like

My_multiple_Range =
Range(Cells(x,y),cells(x+2,y+2):Cells(x+7,y),cells (x+7,Y+7)

So that I can use x and y as variables
John

Gary Keramidas wrote:
you can try this:

Set My_Multiple_Range = Union(Range("a1:a3"), Range("a7:a9"))