Selecting range problem

[dorre]

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]

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]

excellent advice
dorre

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