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.