Recalculate after filter/sort

Louis

New Member
Joined
Apr 14, 2002
Messages
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
How does one "selectively calculate" formulas?

You can try either SHIFT+F9 to recalculate the active sheet only, or you can write a simple macro to recalculate only the selected cells:

Code:
Sub rangecalc()
selection.Calculate
End Sub

I have that in my macro workbook linked to CTRL+J.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top