selecting multiple ranges

[Guest]

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?

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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.