VBA faster calculating

Question

I'm newbie in VBA world. I have recently work with a spreadsheet with over 25000 rows and about 50 columns. Every time I calculate this sheet, it takes about 30 minutes to 1 hour to perform the calculation. Basically I have a row with basic formulas and the following rows just differs data from this row (i.e. the same formulas).

For example, I have a data in sheet Cal from A to N, and range from M to AR is calculated based on those data. Row 1 is "master" formulas, and traditionally I fill down this row to the end and press Save to calculate. It takes me 30mins - 1h. Then I build a macro but the problem about time has not been solved yet. The Macro is also attached in Module 1 of the file. Also the main code is:

Here is my code:

Dim n As Long, i As Long
ThisWorkbook.Activate
Sheet9.Cells(3,1).Select
n = Range(Selection, Selection.End(xlDown)).Rows.Count
With ThisWorkbook.Worksheets(9)
Range(Cells(3, 13), Cells(3, 44)).Select
Selection.Calculate
Selection.Copy
For i = 4 To n + 2
With Range(Cells(i, 13), Cells(i, 44))
.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Calculate
.Value = .Value2
End With
Next i
End With

I hope someone could review and adjust my code or suggest any ideas to perform the calculation faster. I have heard that if code is optimized properly, it just takes about 10 mins to calculation 350.000-row data.

Please help me. Any help would be appreciated.

Thank you.

Answer 1

Just a couple of things:

With ThisWorkbook.Worksheets(9)
Range(Cells(3, 13), Cells(3, 44)).Select

You miss a . here, should be .Range, without that your With doesn't have any effect.

Selection.Calculate

Don't need to select before calculate, you could immediately do .Range(..).Calculate
(Is it really necessary in your logic to calculate before copy)

With Range(Cells(i, 13), Cells(i, 44))

Again, . is missing, should be .Range...

For i = 4 To n + 2
With Range(Cells(i, 13), Cells(i, 44))
.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Calculate
.Value = .Value2
End With
Next i

Do you really need to do this in sequence?? You could just do:

.Range(Cells(3,13),Cells(3,44).Copy 
.Range(Cells(4,13),Cells(n+2,44).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False
.Calculate
.Range(Cells(4,13),Cells(n+2,44).Value = .Range(Cells(4,13),Cells(n+2,44).Value2

You can also find some further tips here: https://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/5925/switch-off-properties-during-macro-execution#t=201706191115061969896