How to optimize the performance of the VBA code?

Question

I have troubles in running the following codes. Basically the idea is to sort the list and copy the unique records to another sheet. But due to the large number of records (160000 rows). My code always hanged and could not stop calculation.

Columns("A:A").Insert Shift:=xlToRight
Range("A1").Value = "Reference2"
Range("A2").Formula = "=B2&F2&N2"
Range("A2").AutoFill Destination:=Range("A2:A160000")

Range("N1").Value = "Day"

Range("N2").Formula = "=DAY(G2)"
Range("N2").AutoFill Destination:=Range("N2:N160000")

Columns("A:N").Select
Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Columns("A:N").Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Columns("A:N").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Sheets("Fairbanks Data").Range("A1:N" & Range("B1").End(xlDown).Row).Select
Selection.Copy

Sheets("Fairbanks Data(Edited)").Cells(Rows.Count, "A").End(xlUp).PasteSpecial xlPasteValues
'my code always stopped at here and did the calculation

Sheets("Fairbanks Data").Delete

Sheets("Fairbanks Data(Edited)").Range("A1:A160000").AdvancedFilter     Action:=xlFilterCopy, CopyToRange:=Sheets("Step1Raw").Range("A2"), Unique:=True

Is that any way I can improve the speed? Thank you for your time!

Moosli · Accepted Answer · 2015-01-29 07:30:18Z

There are a few Rules for VBA with what you can make your code faster.

Rule #1. Don't Copy and Paste

The Copy and Paste (or PasteSpecial) functions are slow. It is about 25 times faster to use the following to copy and paste values.

Range("A1:Z100").value = Range("A101:Z200").value

If you are doing it this way your Code will Probably work. There is maybe a problem with the Mamory if your are doing this on so many Rows.

Rule #2. Calculation

Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed. This may cause your workbook to recalculate too often, which will slow down performance. You can prevent Excel from recalculating the workbook by using the statement:

Application.Calculation = xlCalculationManual

At the end of your code, you can set the calculation mode back to automatic with the statement:

Application.Calculation = xlCalculationAutomatic

Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells. If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method like Worksheets(1).Calculate.

Rule #3. ScreenUpdating

The Other Speed Problem with VBA is, every time VBA writes data to the worksheet it refreshes the screen image that you see. Refreshing the image is a considerable drag on performance. The following command turns off screen updates.

Application.ScreenUpdating = FALSE

At the end of the macro use the following command to turn screen updates back on.

Application.ScreenUpdating = TRUE

eirikdaude · Accepted Answer · 2015-01-29 08:46:29Z

I have these two subs which I tend to put before and after any code I run.

' 01.
Sub deaktiver()
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  ' ActiveSheet.DisplayPageBreaks = True 'note this is a sheet-level setting
End Sub
' 02.
Sub reaktiver()
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.DisplayStatusBar = True
  Application.Calculation = xlCalculationAutomatic
  ' ActiveSheet.DisplayPageBreaks = True 'note this is a sheet-level setting
End Sub

Obviously you need to know, what the different settings do, as some of the settings will prevent calculations to happen or events to fire, as Moosli explained above.

Most (all?) of what's in the code above I found here, which also goes into quite a few other ways to make your code more efficient.

Collectives™ on Stack Overflow

How to optimize the performance of the VBA code?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related