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

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

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

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.

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

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



--

Dave Peterson

Some others:

with activesheet
set myrng = union(.rows(myrow).cells(1).range("A1:c1"), _
.rows(myrow).cells(1).range("F1:J1"))
end with

with activesheet
set myrng = intersect(.rows(myrow), .range("a:c,f:j").entirecolumn)
end with


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



--

Dave Peterson

--

Dave Peterson

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