VBA set range to non-continuous with variables

[rpaulson]

hello all,

not sure what to code I need to select the range

startrow =2
endrow = 15

startcol=10
end col=50
colstep = 10


for r = startrow to endrow
for c = startcol to endcol step colstep

set my Range=???
....

any ideas??

Ross

 

[Fluff]

How about

Sub chk()
Dim rng As Range

Set rng = Intersect(Rows("2:15"), Range("J:J,T:T,AD:AD,AN:AN,AX:AX"))
End Sub

 

[JLGWhiz]

Or this would start with a single cell and then expand with each iteration of the loops.

Set myRange = Range(Cells(2, 10), Cells(r, c))

 

[mikerickson]

Perhaps

startrow =2
endrow = 15

startcol=10
end col=50
colstep = 10

Set myRng = Cells(startRow, startCol)

For r = startRow to endRow
    For c = startCol to endCol Step 10
        set myRng = Application.Union(myRng, Cells(r, c))
    Next c
Next r

 

[rpaulson]

Mikeerickson... you post you resposne while i was trying mine below. look thik your will work.


let me re-phrase:

the 5 variables will be tied to input boxes that the user will enter.

startrow =inputbox ("enter START row")
endrow = inputbox ("enter END row")

startcol=inputbox ("enter START column")
endcol=inputbox ("enter END column")
colstep = inputbox ("enter column STEP")


for r = startrow to endrow
for c = startcol to endcol step colstep

set my Range=???
....

later in the macro I will do some things on the range, like countif values, setting formats etc..

Ross

 

[rpaulson]

Mikeerickson solution works for most of the thing I need to do with the range (Format, set fonts etc.)

but now I need a countif

ex:
x = WorksheetFunction.CountIf(myRange, "Joe")

the above will gives a error try to count how many times Joe is in the range.

 

[mikerickson]

There is no discontinous range that will work properly with COUNTIF.

It would be best to count "Joes" while assembling the range.

Alternatly, if "Joe" only appears in the "good" cells, you could COUNTIF the whole sheet.