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

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

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