Recalculating Excel Sheets with activated autofilter
 

Hi all

I wrote a solution which recalculates the whole workbook (Excel 2002 SP2) in some functions. Everything works fine, but if the user activates the autofilter for some big sheets with complex formulas, the performance is terrible. Without activated filter, recalculation is finished in 10-20 seconds, with activatet autofilter it will take 2-3 minutes

Any ideas how to get a better performance

Recalculating Excel Sheets with activated autofilter
 

Hi BVA,

selecting any filter in autofilter mode triggers a recalcuation of all the
formulae in the list, even if none of their precedents have changed.

So maybe what is happening is that in a normal recalc very few of these
formulae are being calculated because very few precedents have changed, but
when the filter is activated all the formulae are being recalculated.

You could check this by timing ctrl/alt/f9 , if this takes 2-3 minutes it
would tend to confirm the explanation.

If this explanation is correct then you need to speed up the formulae, so
post an example of a slow formula and we will see if there is a faster way.


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"BVA" wrote in message
...
Hi all,

I wrote a solution which recalculates the whole workbook (Excel 2002 SP2)
in some functions. Everything works fine, but if the user activates the
autofilter for some big sheets with complex formulas, the performance is
terrible. Without activated filter, recalculation is finished in 10-20
seconds, with activatet autofilter it will take 2-3 minutes.

Any ideas how to get a better performance?