How to select non-contingous cells

[Johannes]

I want to select every second cell to the left of the active cell on the same row. So why doesn't the code below work?


Range(ActiveCell, ActiveCell.Offset(2, 0), _
ActiveCell.Offset(4, 0), ActiveCell.Offset(6, 0), _
ActiveCell.Offset(8, 0), ActiveCell.Offset(10, 0), _
ActiveCell.Offset(12, 0), ActiveCell.Offset(14, 0), _
ActiveCell.Offset(16, 0), ActiveCell.Offset(18, 0)).Select

Probably a very simple solution, as usual..

Thanks in advance.

[Jacob Hilderbrand]

Using Range the way you are should have two cells, the start of the range and the end of the range.

We can do this with a simple loop.


Option Explicit

Sub SelectRange()
Dim i As Long
Dim MyRange As Range
Set MyRange = ActiveCell
For i = 2 To 18 Step 2
Set MyRange = Union(MyRange, ActiveCell.Offset(i, 0))
Next i
MyRange.Select
End Sub

[Johannes]

Jacob, thanks!

Your solution works very well. Only thing is that I had made a logical error in my initial offset function, so your code selects every second row (vertically) instead of every second colum (horizontally). Just in case anybody else should want to make use of the solution, moving the i to the position below will result in a horizontal selection:

Set MyRange = Union(MyRange, ActiveCell.Offset(0, i))
Thanks again!

[Jacob Hilderbrand]

You're Welcome

Take Care

[sixth sense]

Hi!
Another solution to that.


Sub sel()
tmp = ""
Range("a1").Select
For i = 2 To 18 Step 2
tmp = tmp & ActiveCell.Offset(0, i).Address & ","
Next i
Range(Left(tmp, Len(tmp) - 1)).Select
End Sub

[Johannes]

Sixth sence,

Thank you, your solution works very well also! Goes to prove yet one more time that so many roads leads to Rome..

Johannes