Range in VBA

[Guest]

Hello,

this is my range:
Set myRng = .Range("E3", .Cells(.Rows.Count, "E").End(xlUp))
It is possible to set my range to jump 2 rows.
Example. E3, E5,E7,E9 INSTEAD E3,E4,E5,E6

 

[JE McGimpsey]

One way:

Dim i As Long
Set myRng = .Cells(3, 5)
For i = 5 To .Cells(.Rows.Count, 5).End(xlUp).Row Step 2
Set myRng = Union(myRng, .Cells(i, 5))
Next i
End With

 

[Dave Peterson]

Depending on what you want to do, maybe you could just loop through your cells
and keep track if the row is even or odd.

for each mycell in myrng.cells
if mycell.row mod 2 = 0 then
'it's even, do nothing
else
'do the work
end if
next mycell

Or you could build the range the way you want it:

dim myRng as range
dim LastRow as long
dim iCtr as long
with activesheet
set myrng = .range("e3")
lastrow = .cells(.rows.count,"E").end(xlup).row
for ictr = 5 to lastrow step 2 'start with 5 since myrng already has E3 in it
set myrng = union(myrng, .cells(ictr,"E"))
next ictr
end with