hello,

When using the function, it works the first time.

example:
A1:A10 are "Null"
into A11 put the formula =countblanks()
==the result in A11 is 10

Now insert the value 5 in A5
== the result is still 10.

I added Application.volatile in the code of Mike H
as you have suggested.
With the same example as above the result
becomes 4 (the number of blank cell above A5)

it is perhaps the use of 'ActiveCell' that made this behaviour to occur.

I have changed the code of Mike
and replace 'ActiveCell' with
'Application.Caller'.
It seems to improve the behaviour of the function.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''
Function countnull()
Dim x
Application.Volatile
If TypeName(Application.Caller) = "Range" Then
For x = Application.Caller.Row - 1 To 1 Step -1
If Cells(x, Application.Caller.Column).Formula = vbNullString Then
countnull = countnull + 1
Else
Exit Function
End If
Next x
End If
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''





"Niek Otten" a écrit dans le message de groupe de
discussion : ...
After the first line, add:

Dim x as Long
Application.Volatile

The first line because you might get a compile error if you use Option
Explicit, the second because the function will otherwise not recalculate
if you fill a cell above the calling cell afterwards.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike H" wrote in message
...
Hi,

Blank is an often confusing term with regard to Excel and here I've taken
you literally i.e. nothing on the cell, totally empty.

Call with

=countblanks()

Function countblanks()
For x = ActiveCell.Row - 1 To 1 Step -1
If Cells(x, ActiveCell.Column).Formula = vbNullString Then
countblanks = countblanks + 1
Else
Exit Function
End If
Next
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ytayta555" wrote:

HI , and a good day to all programmers

I need a UDF , which to count non blank cells
above the cell which contain this UDF . When
the UDF find the first non blank cell , to stop
the count .

Example :

A1 - nonblank
A2 - blank
A3 - blank
A4 - blank
A5 - UDF

here , the result of UDF from cell A5 must be 3 .
I need to count only above in the column , not in the
left or right .

Thank you
.