CODEWITHSUNDEEP
excelvba
Clauric
Clauric

Reputation: 1896

How to calculate only the active worksheet in Excel?

I have a very large number of calculations across multiple worksheets (10+ sheets, 30000+ rows, 250+ columns, about 50% with dependencies).

I have a VBA program that runs through a list of variables, changing them one at a time. This change the values in all the cells with formulae.

I don't need all the sheets recalculated every time I change a variable.

Is there a way to recalculate only the active worksheet or a specified worksheet?

Upvotes: 7

Views: 72616

Answers (2)

Ben
Ben

Reputation: 31

One way to do it without VBA is to make the formulas to be dependent upon a boolean cell. If the bool is true, then they calculate, otherwise they do not.

Then in this boolean cell, type this formula: =CELL("filename") = CELL("filename", [B1]). Then place a simple toggle button on this sheet that is linked to any cell in the worksheet. Then, whenever the toggle button is pressed, the linked cell will change values, which will cause the formula in the boolean cell to produce TRUE rather than FALSE, and all the cells that are dependent upon this boolean cell will then calculate.

Example #1

Example #2

This won't keep the cells from calculating, but it will make their calculations super quick.

Upvotes: 3

tea_pea
tea_pea

Reputation: 1542

ActiveSheet.Calculate

or

vbasheetname.Calculate

or

Worksheets("Sheet1").Calculate

It's better practise to avoid using activesheet if possible in your code (there's only rarely a need for it), so try and stick to the second two.

Upvotes: 19

Related Questions