Recalculate after filter/sort

Louis · Apr 15th, 2002, 07:05 AM

Can anyone help me with the following problem?

I have a large workbook with on one sheet a table with an autofilter. When I use this autofilter to select certain records or when I sort it, the whole workbook is recalculated (it turns out that sorting/filtering triggers the recalculation). This takes quite a long time, while it is not necessary. How can I get round this problem?

Possible solutions:
- set the calculation mode to manual...but I still want to calculate some cells (eg the SUBTOTAL formulas on the same sheet)...
- use of the calculate event...but I can only avoid recalculation of the whole workbook by entering code that sets the calculation mode to manual, but then it won't update anything anymore and also the calculate event won't be triggered in the future...
- use of the filter or sort event...but it does not exist (?)...

The problem does not seem too difficult, but I still have not been able to solve it.

Tom Schreiner · Apr 15th, 2002, 07:45 AM

Hi
Depending on how many different cells you are wanting to calculate along with the filter, you may want to try this.
Place a button on your sheet which will disable calculation for the entire application. Perform your filtering. Then on another button, a macro which selectively calculates your chosen ranges and will also re-enable calculation altogether.
If your autofilter performs the same operation with the same parameters each time, you could just automate the whole process into one procedure if you like.
Am I understanding you?
Thanks,
Tom

Louis · Apr 15th, 2002, 08:00 AM

TsTom, thanks for answering!

If you use "xlcalculationmanual" and "xlcalculationautomatic" to disable and enable calculation it will finally recalculate the whole workbook anyway, when clicking the second button (or is this not what you mean?).

Moreover, to keep the sheet user-friendly, it is not preferable to ask the user to click one or two buttons whenever he sorts or autofilters.

But thanks for helping me further with your solution.

Louis · Apr 15th, 2002, 01:00 PM

Maybe I should rephrase my question into a more general one:

Are the default events as defined by visual basic (beforedoubleclick, open, change etc.) the only ones available or is it possible to add new ones yourself in some way? In my case, is there a way to trap an autofilter event or a sort event?

Thanks

UHsoccer · Yesterday, 01:33 PM

How does one "selectively calculate" formulas?

Yesterday, 01:33 PM	#5
UHsoccer Board Regular Join Date: Apr 2002 Location: Detroit Posts: 792	Re: Recalculate after filter/sort How does one "selectively calculate" formulas? __________________ Excel 2003 and 2007 on XP as well as Excel 2010 on 64bit Windows-7

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 07:05 AM	#1
Louis New Member Join Date: Apr 2002 Posts: 11	Can anyone help me with the following problem? I have a large workbook with on one sheet a table with an autofilter. When I use this autofilter to select certain records or when I sort it, the whole workbook is recalculated (it turns out that sorting/filtering triggers the recalculation). This takes quite a long time, while it is not necessary. How can I get round this problem? Possible solutions: - set the calculation mode to manual...but I still want to calculate some cells (eg the SUBTOTAL formulas on the same sheet)... - use of the calculate event...but I can only avoid recalculation of the whole workbook by entering code that sets the calculation mode to manual, but then it won't update anything anymore and also the calculate event won't be triggered in the future... - use of the filter or sort event...but it does not exist (?)... The problem does not seem too difficult, but I still have not been able to solve it.

Apr 15th, 2002, 07:45 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Depending on how many different cells you are wanting to calculate along with the filter, you may want to try this. Place a button on your sheet which will disable calculation for the entire application. Perform your filtering. Then on another button, a macro which selectively calculates your chosen ranges and will also re-enable calculation altogether. If your autofilter performs the same operation with the same parameters each time, you could just automate the whole process into one procedure if you like. Am I understanding you? Thanks, Tom

Apr 15th, 2002, 08:00 AM	#3
Louis New Member Join Date: Apr 2002 Posts: 11	TsTom, thanks for answering! If you use "xlcalculationmanual" and "xlcalculationautomatic" to disable and enable calculation it will finally recalculate the whole workbook anyway, when clicking the second button (or is this not what you mean?). Moreover, to keep the sheet user-friendly, it is not preferable to ask the user to click one or two buttons whenever he sorts or autofilters. But thanks for helping me further with your solution.

Apr 15th, 2002, 01:00 PM	#4
Louis New Member Join Date: Apr 2002 Posts: 11	Maybe I should rephrase my question into a more general one: Are the default events as defined by visual basic (beforedoubleclick, open, change etc.) the only ones available or is it possible to add new ones yourself in some way? In my case, is there a way to trap an autofilter event or a sort event? Thanks