find first data entry in range
 

Hi everybody
I have a little problem. I have a macro which copies data in a colum
to a new sheet. This column contains a lot of cells with 0 as entry. I
order to calculate a correct average I remove all the 0 values and the
use "average". Now I need to find the first and last cells in th
column where there is data inorder to correspond to these rows. Can an
one help med pls?

Regards
Jona

--
joh
-----------------------------------------------------------------------
joho's Profile: http://www.excelforum.com/member.php...fo&userid=2698
View this thread: http://www.excelforum.com/showthread.php?threadid=40206

find first data entry in range
 

If you have removed the zeros and only the data is remaining, then you can
get just the cells containing number constants in column 3 as an example
with:

Dim rng as Range
On Error Resume Next
set rng = columns(3).specialcells(xlconstants,xlNumbers)
On Error goto 0
if not rng is nothing then
for each cell in rng

Next

or
ans = Application.Average(rng)


--
Regards,
Tom Ogilvy


"joho" wrote in message
...

Hi everybody
I have a little problem. I have a macro which copies data in a column
to a new sheet. This column contains a lot of cells with 0 as entry. In
order to calculate a correct average I remove all the 0 values and then
use "average". Now I need to find the first and last cells in the
column where there is data inorder to correspond to these rows. Can any
one help med pls?

Regards
Jonas


--
joho
------------------------------------------------------------------------
joho's Profile:
http://www.excelforum.com/member.php...o&userid=26984
View this thread: http://www.excelforum.com/showthread...hreadid=402064