[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Selecting multiple ranges

RobWN

12/17/2006 7:57:00 PM

I want to select multiple contiguous row ranges in non-contiguous columns but can't seem
to figure it out.

The macro recorder equivalent is
Range("U2:U12,Y2:Y12").Select

This is an abbreviation of what I want as I'm trying to select five ranges in all.

From the VBA "Help" it appears as if I'd have to use "Union", is there another way (or
I'll have a statement that will go on forever!)?

--
Regards;
Rob
------------------------------------------------------------------------
4 Answers

Jon Peltier

12/17/2006 8:04:00 PM

0

What is the problem you're having?

Range("U2:U12,Y2:Y12").Select

works as expected, and so does this, with five areas:

Range("A2:A12,D2:D12,L15:P25,U2:U12,Y2:Y12").Select

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______


"RWN" <NotLikely@mts.net> wrote in message
news:O4zi7UhIHHA.4848@TK2MSFTNGP04.phx.gbl...
>I want to select multiple contiguous row ranges in non-contiguous columns
>but can't seem
> to figure it out.
>
> The macro recorder equivalent is
> Range("U2:U12,Y2:Y12").Select
>
> This is an abbreviation of what I want as I'm trying to select five ranges
> in all.
>
> From the VBA "Help" it appears as if I'd have to use "Union", is there
> another way (or
> I'll have a statement that will go on forever!)?
>
> --
> Regards;
> Rob
> ------------------------------------------------------------------------
>
>


Tom Ogilvy

12/17/2006 8:31:00 PM

0

set rng2 = Intersect(Range("U:U,Y:Y,AC:AC,AG:AG,AK:AK"),Range("2:12"))
? rng2.Address(0,0)
U2:U12,Y2:Y12,AC2:AC12,AG2:AG12,AK2:AK12


Sub BBB()
Dim rng As Range, rng1 As Range
Set rng = Range("U2:U12")
Set rng1 = rng
For i = 1 To 4
Set rng = Union(rng, rng1.Offset(0, i * 4))
Next
Debug.Print rng.Address(0, 0)

End Sub

also produces
U2:U12,Y2:Y12,AC2:AC12,AG2:AG12,AK2:AK12

--
Regards,
Tom Ogilvy


"RWN" <NotLikely@mts.net> wrote in message
news:O4zi7UhIHHA.4848@TK2MSFTNGP04.phx.gbl...
>I want to select multiple contiguous row ranges in non-contiguous columns
>but can't seem
> to figure it out.
>
> The macro recorder equivalent is
> Range("U2:U12,Y2:Y12").Select
>
> This is an abbreviation of what I want as I'm trying to select five ranges
> in all.
>
> From the VBA "Help" it appears as if I'd have to use "Union", is there
> another way (or
> I'll have a statement that will go on forever!)?
>
> --
> Regards;
> Rob
> ------------------------------------------------------------------------
>
>


RobWN

12/17/2006 8:31:00 PM

0

Jon,. thanks.
I was using the recorder for an example only.
In real life my ranges will be variable and I could never figure out how to replicate the
recorder.

Typically I will select as;
Range(Cells(ro,co),Cells(r,c)).Select
Where the r,c values may be set by a CurrentRegion.rows|columns.count, for example.

Am I missing something?
(I admit to "getting in trouble" with the Range function)
--
Regards;
Rob
------------------------------------------------------------------------
"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
news:e4Zv0ZhIHHA.3676@TK2MSFTNGP03.phx.gbl...
> What is the problem you're having?
>
> Range("U2:U12,Y2:Y12").Select
>
> works as expected, and so does this, with five areas:
>
> Range("A2:A12,D2:D12,L15:P25,U2:U12,Y2:Y12").Select
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://Pelti...
> _______
>
>
> "RWN" <NotLikely@mts.net> wrote in message
> news:O4zi7UhIHHA.4848@TK2MSFTNGP04.phx.gbl...
> >I want to select multiple contiguous row ranges in non-contiguous columns
> >but can't seem
> > to figure it out.
> >
> > The macro recorder equivalent is
> > Range("U2:U12,Y2:Y12").Select
> >
> > This is an abbreviation of what I want as I'm trying to select five ranges
> > in all.
> >
> > From the VBA "Help" it appears as if I'd have to use "Union", is there
> > another way (or
> > I'll have a statement that will go on forever!)?
> >
> > --
> > Regards;
> > Rob
> > ------------------------------------------------------------------------
> >
> >
>
>

RobWN

12/18/2006 12:35:00 AM

0

Thanks Tom, your second example is what I was looking for.
My version is;
Sub TestI()
Dim rng As Range
Dim rng1 As Range
Set rng = Range(Cells(2, TargetCol), Cells(LastRow, TargetCol))
Set rng1 = rng
For i = 1 To 4
Set rng = Union(rng, rng1.Offset(0, i * 4))
Next
rng.Select
End Sub

My confusion with ranges revolves around the fact that I don't know how to programatically
create the range using an "A1" format.

Again, thanks
--
Regards;
Rob
------------------------------------------------------------------------
"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OUXI9phIHHA.536@TK2MSFTNGP02.phx.gbl...
> set rng2 = Intersect(Range("U:U,Y:Y,AC:AC,AG:AG,AK:AK"),Range("2:12"))
> ? rng2.Address(0,0)
> U2:U12,Y2:Y12,AC2:AC12,AG2:AG12,AK2:AK12
>
>
> Sub BBB()
> Dim rng As Range, rng1 As Range
> Set rng = Range("U2:U12")
> Set rng1 = rng
> For i = 1 To 4
> Set rng = Union(rng, rng1.Offset(0, i * 4))
> Next
> Debug.Print rng.Address(0, 0)
>
> End Sub
>
> also produces
> U2:U12,Y2:Y12,AC2:AC12,AG2:AG12,AK2:AK12
>
> --
> Regards,
> Tom Ogilvy
>
>
> "RWN" <NotLikely@mts.net> wrote in message
> news:O4zi7UhIHHA.4848@TK2MSFTNGP04.phx.gbl...
> >I want to select multiple contiguous row ranges in non-contiguous columns
> >but can't seem
> > to figure it out.
> >
> > The macro recorder equivalent is
> > Range("U2:U12,Y2:Y12").Select
> >
> > This is an abbreviation of what I want as I'm trying to select five ranges
> > in all.
> >
> > From the VBA "Help" it appears as if I'd have to use "Union", is there
> > another way (or
> > I'll have a statement that will go on forever!)?
> >
> > --
> > Regards;
> > Rob
> > ------------------------------------------------------------------------
> >
> >
>
>