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.
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.