How to get the VBA to calculate in loops or run more efficiently

[Keewi82]

Hi All

I have a really large range that requires mapping and recalculation and is taking a long time to recalc, any suggestions and ideas on how to make this more efficient? was thinking a loop or defining integers but i am really unfamiliar with this function. mapping is on 20457 rows and 34 columns and it uses index match as the primary formula that looks over 2 other data sets the largest being 306000 rows and about 40 columns

Currently macro is:

Sub UpdateCourseDetailed()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("P16:AW16").AutoFill Destination:=Range("P16:AW" & Cells(Rows.Count, "C").End(xlUp).Row)
Call FastRecalcSheets(True)
'Output.ResetFiltering
Range("P17:AW" & Cells(Rows.Count, "C").End(xlUp).Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Call Fast Relcalc part

Sub FastRecalcSheets(Optional SilentMode As Boolean = False)
CRSEDETAILEDDATA.Calculate

If Not SilentMode Then
MsgBox "Recalculation complete."
End If
End Sub

 

[RedBeard]

This should speed things up, unless my addled brain tricks me:

Range("P17:AW" & Cells(Rows.Count, "C").End(xlUp).Row).Copy
Range("P17:AW" & Cells(Rows.Count, "C").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[Keewi82]

Thanks RedBeard
it has made the last step more efficient but unfortunately the calculation is stage is still really laggy

 

[RedBeard]

Well, considering how much data you have (and I assume quite a few formulas), I can't think of a way to make that bit go any faster.

Someone smarter here might know of a way though.

 

[Keewi82]

No props to you for helping in the first place!
Yeah hopefully someone can help me try and perform the calculations in groups of 500 or something along those lines