How can you represent this in cell notation so you can use variables?

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

thanks
John

you can try this:

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

--


Gary


"John" wrote in message
...
How can you represent this in cell notation so you can use variables?

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

thanks
John

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"))

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"))

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"))



--

Dave Peterson

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"))

It looks like that sets the individual cells rather than two ranges.
I'll give it a try tough.
thanks
John


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.

How about this

Set My_Multiple_Range = Union(Range("a1:a3"),Range("a7:a9"))
--
HTH,
Barb Reinhardt



"John" wrote:

How can you represent this in cell notation so you can use variables?

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

thanks
John