Excel doesn't know when to recalculate your formula because no cells are
used as arguments.
You can add "Application.Volatile" as the first line in your function; then
it will always be recalculated.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"@lan " wrote in message
...
Hi,
in a worksheet "test" with autofilter mode, I'm using the formula:

=nb_row_aera(1)

which refers to my VBA function :

Function nb_row_area(n as Long) As Long
nb_row_area =

Worksheets("test").AutoFilter.Range.SpecialCells(x lCellTypeVisible).Areas(n)
..Rows.Count
End Function

the problem is that the result is always the nb of rows in the initial
range for Autofilter even if filters are activated in the worksheet!
On the contrary, the same code placed into a macro provides good
results!!!

Sub Pr_nb_row_area()
n = 1
MsgBox

Worksheets("test").AutoFilter.Range.SpecialCells(x lCellTypeVisible).Areas(n)
..Rows.Count
End Sub

Why and how should I modify the function?


---
Message posted from http://www.ExcelForum.com/