FormulasFlatten

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.

CodeFunctionName What is this?

Public

Tested

Original Work

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 Mydev.net

Linkedin Login

TheMessage, Optional Column1, Optional Row1, Optional Column9, Optional Row9, Optional Row2

Views 3,271

Downloads 1,309

CodeID
DB ID