I have thousands of cells that refer to a dynamic named range. They slow down my workbook tremendously, because the named range is recalculated every time the sheet calculates which forces those thousands of cells to recalculate and each one of those thousands of cells forces the named range to recalculate again.
Exmple:
The formulas in cells $C$12:$C$10,000+ force myRange to recalculate 10,000+ times.![]()
Please Login or Register to view this content.
I thimk I have discovered a way to avoid all the calculations.
Range("Calculate") calculates what the value of CodesCount should be and automatically changes the value of CodesCount and forces myRange to recalculate which forces those thousands of cells to recalculate.![]()
Please Login or Register to view this content.
The only issue I have with this solution is this:
If I have a 2nd dynamic named range dependent on CodeCount, only 1 of them gets calculated when CodesCount changes.
As far as I can tell it's not a problem. Any formula that is dependent on my2ndRange forces it to recalculate, and if I use my2ndRange in a macro, it also forces it to recalculate, but it's unnerving for it not to recalculate automatically.![]()
Please Login or Register to view this content.
Before I change all my RefersTos to use my UDF(), does anyone see a problem with this solution?
Also, does anyone have an idea why my2ndRange doesn't automatically recalculate?
Bookmarks