function called from worksheet and bad result!
 

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 initia
range for Autofilter even if filters are activated in the worksheet!
On the contrary, the same code placed into a macro provides goo
results!!!

Sub Pr_nb_row_area()
n = 1
MsgBo
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

function called from worksheet and bad result!
 

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/

function called from worksheet and bad result!
 

Unfortunately no change with (even with F9):

Function nb_row_area(n As Long) As Long
Application.Volatile
nb_row_area
Worksheets("test").AutoFilter.Range.SpecialCells(x lCellTypeVisible).Areas(n).Rows.Count
End Function

should I activate an option for use of Application.Volatile

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

function called from worksheet and bad result!
 

Several methods don't work when used as a UDF. I believe specialcells is
one of those.

You may have to loop throught the range in your UDF or better, use the
already builtin Subtotal worksheet function.

=Subtotal(3,A1:A100)-1

Assumes that column A will contain an entry for each record.

--
Regards,
Tom Ogilvy



"@lan " wrote in message
...
Unfortunately no change with (even with F9):

Function nb_row_area(n As Long) As Long
Application.Volatile
nb_row_area =

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

should I activate an option for use of Application.Volatile?


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

function called from worksheet and bad result!
 

in fact I would like to build a function to return the number o
different values in column A for example taking current filtering int
account (I think there's no appropriate function with subtotal).

My whole procedure using SpecialCells(xlCellTypeVisible) works well bu
not the same code in the function!

Do you have any other suggestions or tricks to get round the problem

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

function called from worksheet and bad result!
 

Loop through the cells of the filtered area and check if the row is hidden
or not.

--
Regards,
Tom Ogilvy

"@lan " wrote in message
...
in fact I would like to build a function to return the number of
different values in column A for example taking current filtering into
account (I think there's no appropriate function with subtotal).

My whole procedure using SpecialCells(xlCellTypeVisible) works well but
not the same code in the function!

Do you have any other suggestions or tricks to get round the problem?


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

function called from worksheet and bad result!
 

effectively method Hidden works in UDF unlike SpecialCells.

Tom and Niek, thank you

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