Fill in formulas from 1st row, then convert them to flat values to avoid Excel Calculation delays in large complicated formulas
When you have complicated formula running for huge list of rows (say bunch of SumIFs, Matchs, Offsets, etc. in 140k rows), Excel calculation causes delay everytime a cell is changed
That is because Calculation is on
We may turn it off, but other cells and calculation will be delayed
So, a simple solution is to convert all these formulas causing delays into flat values (Paste-As-Values)
And because we still do not want to turn calculation off all the time and allow some nice user experience, FormulasFlatten is born (1st called RefreshLevel2)
Here is what this sub will do
- Ask user if want to go with it or not
- Turn calculation off
- Update certain column by filling formula down from first row
- Flat all cells except 1st row, Flat means convert formula into flat results, like Paste-As-Values
- Do calculation
- Turn Calculation On
This way, we got those columns refreshed with most recent values without sacrificing efficiency.
Access is limited.
Login here using your Linkedin account. to see or download code.
No registration, not another password to remember, login using your Linkedin account and have access to all public codes in Boldcodes.com
Linkedin LoginViews 3,271
Downloads 1,309
ANmarAmdeen
701