Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE a range in a column if another column's range equals a val | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
CountIf first column range = "Word" and second column range <> 0 | Excel Worksheet Functions | |||
Transfering VBA Array Column Range to Excel Column Range | Excel Programming |