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.