Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting multiple ranges
I am working on a project where I need to perform multiple actions on a
selection of multiple ranges. I am currently using : Worksheets("Current Mth").Range(Cells(1, 2), Cells(2, C - 1)) Can I use something similar to the Range("a1:b1.V1:v4") format? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting multiple ranges
Yes you can use this format: Range("A1:B1,A5:B5").Select Note the comma between the two ranges (not a full stop as in the sample provided. You can even go for more than two ranges like in this example (I haven't tested the limit): Range("A1:B1,A5:B5, A10:B10, A15:B15").Select There limitation of this code though is that it assumes the range it on the Active worksheet. If you want to perform actins to multiple ranges on specific worksheet then you'll need to prefix the range with it like this. Worksheets("Sheet2").Range("A1:B1,A5:B5, A10:B10, A15:B15").Interior.Color = vbRed Note that you can't select or activate the cell on another worksheet. If you want to do several actions with the range, consider assigning the range to a variable like this: Dim rng As Range Set rng = Worksheets("Sheet2").Range("A1:B1,A5:B5, A10:B10, A15:B15") rng.Interior.Color = vbRed Good luck OfficeHacker "Helen" wrote: I am working on a project where I need to perform multiple actions on a selection of multiple ranges. I am currently using : Worksheets("Current Mth").Range(Cells(1, 2), Cells(2, C - 1)) Can I use something similar to the Range("a1:b1.V1:v4") format? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting multiple ranges
Sub Tester3()
Dim rng As Range, C As Long Dim F As Long C = 3 F = 24 With Worksheets("Current Mth") Set rng = .Range(.Cells(1, 1), .Cells(1, C - 1)) Set rng = Union(rng, .Range(.Cells(1, 22), .Cells(4, F - 2))) End With Debug.Print rng.Address(0, 0) End Sub produces A1:B1,V1:V4 -- Regards, Tom Ogilvy "Helen" wrote in message ... I am working on a project where I need to perform multiple actions on a selection of multiple ranges. I am currently using : Worksheets("Current Mth").Range(Cells(1, 2), Cells(2, C - 1)) Can I use something similar to the Range("a1:b1.V1:v4") format? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting multiple ranges
To complete the answer, using A1 style notation as an argument to Range, if
you are going to have a variable column value, would be more trouble than it is worth. If you are only varying the row, then you could concatenate in a variable row value fairly easily. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub Tester3() Dim rng As Range, C As Long Dim F As Long C = 3 F = 24 With Worksheets("Current Mth") Set rng = .Range(.Cells(1, 1), .Cells(1, C - 1)) Set rng = Union(rng, .Range(.Cells(1, 22), .Cells(4, F - 2))) End With Debug.Print rng.Address(0, 0) End Sub produces A1:B1,V1:V4 -- Regards, Tom Ogilvy "Helen" wrote in message ... I am working on a project where I need to perform multiple actions on a selection of multiple ranges. I am currently using : Worksheets("Current Mth").Range(Cells(1, 2), Cells(2, C - 1)) Can I use something similar to the Range("a1:b1.V1:v4") format? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting mutliple ranges | Excel Worksheet Functions | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
Selecting ranges | Excel Programming | |||
Selecting ranges with a text box | Excel Programming | |||
selecting ranges | Excel Programming |