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

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

excellent advice
dorre

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>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