Macros suddenly run much slower

Olivierb61 

I had this same problem. Something has clearly changed on MS's side. Some macros which used to take around 30 secs were taking anywhere from 20 mins to 2.5 hours. This was a sudden change early last week. However, we've found a fix. Once we disabled automatic formula recalculation during execution of the macro, everything went back to normal. If anything, faster than before.

At the beginning of each function:

Application.Calculation = xlCalculationManual

At the end of each function:

Application.Calculation = xlCalculationAutomatic

Or, a better way, if you want to preserve the user settings of automatic vs manual calculation following completion of the macro:

Sub x()
 lCalc = Application.Calculation
 
 With Application
 .Calculation = xlCalculationManual
 .ScreenUpdating = False
 .DisplayAlerts = False
 
 ''///your code here
 
 .Calculation = lCalc
 .ScreenUpdating = True
 .DisplayAlerts = True
 End With
End Sub

(Credit to user royUK on https://www.ozgrid.com/ forum for that addition and code snippet)