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

Sub FormulasFlatten(TheMessage, Optional Column1 = "I", Optional Row1 = 5, Optional Column9 = "I", Optional Row9 = 32260, Optional Row2 = 10)
' Column1 is column for formula, 1st column
' Row1 is row number where we have 1st value, it is the one with formulas to fill in from
' Row2 is row of first cell t ostart flatting from, has to be Row1 + 1 at least
' Column9 is last column of that range of formulas, use same as Column1 if you only have 1 column
' Row9 is the row of last cell having formulas to be flat
'
T1 = TheMessage
If TheMessage = "" Then
T1 = "This refreh is only needed when you change values in any of the DB sheets!" & vbCrLf & _
"DB-JWN, DB-Pier1 or DB-MKE" & vbCrLf & _
"It is not needed when you only change selection in this report" & vbCrLf & vbCrLf & _
"Continue with refresh?"
End If
Mss = MsgBox(T1, vbYesNo + vbQuestion)
If Mss = vbNo Then Exit Sub
' Column1 = "I"
' Column9 = "I"
' Row1 = 5 ' first row to start filling formulas from
' Row2 = 10 ' row to start convert formulas int oflat values, should be >= Row1 + 1, rows above this will stay as formula, all rows from Row2 and down will be flat
' Row9 = 32260 ' Last row that will have formula needed to convert into flat, basically last row of range
Application.Calculation = xlCalculationManual
Sheet10.Range(Column1 & Row1, Column9 & Row9).FillDown
Application.Calculate
Sheet10.Range(Column1 & Row2, Column9 & Row9).Value = Sheet10.Range(Column1 & Row2, Column9 & Row9).Value
Application.Calculation = xlCalculationAutomatic
End Sub

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