Selecting range problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dorre - 26 Mar 2006 02:38 GMT

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

Reply to this Message

Dave Peterson - 26 Mar 2006 03:27 GMT

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

> Hi to all
>
[quoted text clipped - 9 lines]
>
> TQ for any clues, Dorre

Signature

Dave Peterson

Reply to this Message

dorre - 26 Mar 2006 03:33 GMT

excellent advice
dorre

>I think I'd just start at the top and loop through each cell looking for
>the
[quoted text clipped - 26 lines]
>>
>> TQ for any clues, Dorre

Reply to this Message