Recalculating Excel Sheets with activated autofilter

[Guest]

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

 

[Charles Williams]

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

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.

 

[Guest]

Hi Charles

thanks for the answer. Unfortunately, the recalculating of the hole workbook with CTRL/ALT/F9 needs about 20 seconds, so the bottleneck aren't the slow formulas..

Does Excel recalculate the autofilter after recalculating a formula in the filtered table? This could be the answer to my problem..

Greeting
BVA

 

[Charles Williams]

Hmmm... are you in Automatic calculation mode? if so switch to manual and
see what happens.


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

Hi Charles,

thanks for the answer. Unfortunately, the recalculating of the hole

workbook with CTRL/ALT/F9 needs about 20 seconds, so the bottleneck aren't
the slow formulas...

Does Excel recalculate the autofilter after recalculating a formula in the

filtered table? This could be the answer to my problem...

 

[Guest]

Same duration in manual mode: 20 seconds

 

[Guest]

Hi Charles,

another thing I just found out: It is not only the activated autofilter. If I switch on the autofilter and calculate, everything is OK (20 seconds). But the more filter criterias I define, the longer takes the calculation (up to 3 minutes).

 

[Charles Williams]

Hi BVA,

What I meant was: are you in Manual calculation mode all the time when using
autofilter?
(nothing to do with ctrl/alt/f9)


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

Hi Charles,

another thing I just found out: It is not only the activated autofilter.

If I switch on the autofilter and calculate, everything is OK (20 seconds).
But the more filter criterias I define, the longer takes the calculation (up
to 3 minutes).

 

[Guest]

Yes, I'm always in manual mode.

 

[Charles Williams]

If you are able to send me a zipped copy of the workbook I will be happy to
look at it.


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