multiple ranges in cell notation

[John]

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

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

thanks
John

 

[Gary Keramidas]

you can try this:

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

 

[Barb Reinhardt]

How about this

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

 

[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

 

[Barb Reinhardt]

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.

 

[Dave Peterson]

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

 

[John]

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

 

[John]

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]

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

 

[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

 

[John]

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