Range until last value of a column

[Scott Steiner]

Hi,

I have a sheet and I want to loop through the values of column B from B2
to B10, so I used this code:

For Each c In Worksheets("TestSheet").Range("B2:B10")
....
Next c

The above code worked but I don't want B10 to be fixed but rather
variable i.e I want to select the range from B2 to last value in column
B, so I tried this:

For Each c Worksheets("TestSheet").Range("B2", Range("B2").End(xlDown))
....
Next c

However, the above didn't work, nothing happens when the code runs. So
my question is: how can I make the range be from B2 to the last value in
column B?

Thanks.

 

[Dave Peterson]

I like the with/end with to save typing:

dim myRng as range
dim c as range

with worksheets("testsheet")
set myrng = .range("b2",.cells(.rows.count,"B").end(xlup))
end with

for each c in myrng.cells
'...
next c

 

[Bob Phillips]

That should work, unless B3 is empty, but others below aren't.

Try

For Each c In Worksheets("TestSheet").Range("B2",
Cells(Rows.Count,"B").End(xlUp).Row)


--

HTH

RP
(remove nothere from the email address if mailing direct)