Set Range

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
Damon,


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

Thanks again,

Kurt
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
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
 

Forum statistics

Threads
1,148,056
Messages
5,744,548
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top