0
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Welcome to StackExchange Code Review! Please review How do I ask a good Question? Specifically, it is best to explain what the code does. This is especially true in the title. Also if you could indent your code to better show the blocks, that would be helpful. \$\endgroup\$ – Stephen Rauch Jun 17 '17 at 5:57
  • \$\begingroup\$ One more thing. For security reasons, many will not take random code (xlsm in this case) from drop box. If the info there is important for a good review, you should consider figuring out to put it into your Question. \$\endgroup\$ – Stephen Rauch Jun 17 '17 at 6:00
1
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ As I run your shorter code, it just calculated Cell(3,13)-Cell(3,44), the remainder is blank. \$\endgroup\$ – Hiếu Nguyễn Phi Jun 30 '17 at 3:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.