Count the number of cells in a range that are not empty

Hi,

Supose I have in column A the folhowing values:
A1 = 1
A2 = 2
A3 = 3

I also have the folowing function in a module:

Please Login or Register  to view this content.

I want to "count the number of cells in a range that are not empty".
I know that I can get result I want using "=counta(A1:A3)", but I want to use the function above.

It's possible?

Regards,
Elio Fernandes.

Why not simply use

=COUNTA(A:A)

There is little point using UDF in place of native functions given in most cases the UDF will be slower.

On an aside - passing col as a Byte is not ideal - better to use Long given 2007+ capacity (merit of Integer type in VBA is debatable)

If you want to then try

Please Login or Register  to view this content.

Entered as
=FindLastRow("A")

or

Please Login or Register  to view this content.

Entered as
=CountUsedCells(A:A)

Hope this helps

And FWIW you'd be far better off passing the Column as a Range to the UDF as opposed to a number thereby negating the need for Application.Volatile
(ie make precedent range explicit as opposed to implicit)

Given UDF's are generally slow(er) making them Volatile has obvious drawbacks.

If you're keen on UDFs it would IMO be a good idea to have a read through of Charles Williams' page on the topic if you've not already:

http://www.decisionmodels.com/calcsecretsj.htm