Set Range

[stapuff]

I been attempting to set a variable range. In the following code I need to get rid of--->Set rng = Range([AS9], [AS9].End(xlDown))<--- and change it to work with the loop. In my worksheet ("AV" & i + 1) = row 9. I would like to set AS9 as the range.

Any suggestions?

Thanks,

Kurt


Dim c As Variant
Dim rng As Range
Dim i As Long
Sheets("sheet1").Select
i = Range("AS3").Row
While Cells(i, 45) <> "Week"
i = i + 1
Wend
ActiveSheet.Range("AV" & i + 1 & ":AV300").ClearContents
Set rng = Range([AS9], [AS9].End(xlDown))
For Each c In rng

 

[Damon Ostrander]

Hi stapuff,

You should be able to do either of these:

Set rng = Range(Cells(i+1,45),Cells(i+1,45).End(xlDown)) 'Column 45 is column "AS"

or

Set rng = Range("AS" & i+1,Range("AS" & i+1).End(xlDown))

Damon

 

[stapuff]

Damon,


I appreciate the post back. i will try your suggestion. I apologize for not getting back sooner.

Thanks again,

Kurt

 

[stapuff]

Damon -

I used Set rng = Range("AS" & i+1,Range("AS" & i+1).End(xlDown)) .

It works very well. I thank you very much.

Kurt

 

[Damon Ostrander]

De nada.