Range until last value of a column
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. |
Range until last value of a column
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 Scott Steiner wrote: 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 |
Range until last value of a column
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) "Scott Steiner" wrote in message ... 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. |
All times are GMT +1. The time now is 02:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com