Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting multiple ranges
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 ------------------------------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting multiple ranges
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").Selec t - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "RWN" wrote in message ... 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 ------------------------------------------------------------------------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting multiple ranges
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting multiple ranges
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" wrote in message ... 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").Selec t - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "RWN" wrote in message ... 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 ------------------------------------------------------------------------ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting multiple ranges
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" wrote in message ... 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" wrote in message ... 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 ------------------------------------------------------------------------ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
Selecting multiple ranges | Excel Programming | |||
Selecting multiple ranges using 'Cells' notation | Excel Programming | |||
selecting multiple ranges | Excel Programming | |||
Selecting ranges | Excel Programming |