Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting range problem
Hi to all
I use the following line to select a range of values. ActiveSheet.Range("A1:C" & ActiveSheet.Range("A1").End(xlDown).Row).Select The problem is that I want to go down to the last row with actual values, not formulas that produce a blank. For example, A1:A100 has the formula =IF(J1="","",SQRT(J1)), dragged down, but there may be values only in A1:A30. I'd like the code to select A1:C30, not A1:C100. Contiguous, non-blank values only. TQ for any clues, Dorre |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting range problem
I think I'd just start at the top and loop through each cell looking for the
first cell that evaluated to "". dim LastCell as range set lastcell = activesheet.range("a1") do if lastcell.value = "" then exit do else set lastcell = lastcell.offset(1,0) end if loop range("a1:c" & lastcell.row).select dorre wrote: Hi to all I use the following line to select a range of values. ActiveSheet.Range("A1:C" & ActiveSheet.Range("A1").End(xlDown).Row).Select The problem is that I want to go down to the last row with actual values, not formulas that produce a blank. For example, A1:A100 has the formula =IF(J1="","",SQRT(J1)), dragged down, but there may be values only in A1:A30. I'd like the code to select A1:C30, not A1:C100. Contiguous, non-blank values only. TQ for any clues, Dorre -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting range problem
excellent advice
dorre "Dave Peterson" wrote in message ... I think I'd just start at the top and loop through each cell looking for the first cell that evaluated to "". dim LastCell as range set lastcell = activesheet.range("a1") do if lastcell.value = "" then exit do else set lastcell = lastcell.offset(1,0) end if loop range("a1:c" & lastcell.row).select dorre wrote: Hi to all I use the following line to select a range of values. ActiveSheet.Range("A1:C" & ActiveSheet.Range("A1").End(xlDown).Row).Select The problem is that I want to go down to the last row with actual values, not formulas that produce a blank. For example, A1:A100 has the formula =IF(J1="","",SQRT(J1)), dragged down, but there may be values only in A1:A30. I'd like the code to select A1:C30, not A1:C100. Contiguous, non-blank values only. TQ for any clues, Dorre -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Help please in selecting range dependent on another range | Excel Programming | |||
Problem selecting a named range | Excel Programming | |||
Selecting a Range inside a range | Excel Programming | |||
Problem selecting a range to print... | Excel Programming |