Turn On AutoCalculations but do not calculate now

[zico8]

Hi,

Is it possible to enable AutoCalculations (after I disabled its) by prevent immediate calculations until normally it would be executed (after some changes in worksheet is done?)

 

[zico8]

Thanks for your replies.
I will set it into manual before filter is setting up, and then after workbook_sheetchange it will switch to automatic again.

But... changing filter is not execute workbook_sheetchange?

 

[shg]

Check this out.

A workbook calculates when any change is made if calculation is set to Automatic. Your code calculates when any change is made and calculation is not set to Automatic.

Explain how that's different?

 

[zico8]

So, to sum up...

I need some solution that will calculate my sheet when some value is changed and Not when table filter is changed.

Is this possible?

 

[shg]

You can put Calculation Options on the QAT for ready access to change.

Why does the table take so long to calculate? Do you have volatile formulas?

 

[zico8]

Yes, I have there some columns with INDEX+MATCH reffering to the other table as well as some COUNTIF and SUMIF. Unfortunately I need them.

What do you meen by adding Calculation Options to the QAT? How it can help me?

Maybe the better solution will be filtering data by running macro that hides unneeded rows instead of Table filter? Would it be faster?

 

[Mentor82]

Hi,
As I already mentioned the application.calculate is not the same as automatic calculation. Set the excel calculation to manual then set the following code in workbook module. The

Workbook_SheetChange​

will run only if the change is made in cell and will run one time calculation. It will not run while using filter in excel.
Check this out, and let me know.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.Calculate
End Sub

 

[mikerickson]

Have you tried

Application.Calculation = xlCalculationSemiautomatic

which sets calculation to automatic, except for data tables.

 

[zico8]

Mikerickson - I need to calculate tables as well;

Mentor82 - it can be solution. I will check tomorrow at work