To make a procedure run faster, it is common practice to include a command at the BEGINNING & END of a process
CODE
Application.ScreenUpdating = False
'...Your code here
Application.ScreenUpdating = True
There is another technique that can contribute to faster, more efficient code processing. Most WorkBook, WorkSheet, Range and other Objects Properties & Methods can be referenced without using the Select or Activate Method. The following comparison test will illustrate the point...
CODE
Sub CompareMethods()
Dim TimeStart As Date, TimeEnd As Date, i As Byte, j As Long
Application.ScreenUpdating = False
For i = 1 To 2
TimeStart = Now
For j = 1 To 65000
Select Case i
Case 1
Sheet1.Cells(j, 2).Value = j
Sheet2.Cells(j, 2).Value = j
Case 2
With Sheet1
.Activate
.Cells(j, 2).Select
Selection.Value = j
End With
With Sheet2
.Activate
.Cells(j, 2).Select
Selection.Value = j
End With
End Select
Next
TimeEnd = Now
Sheet1.Cells(i, 1).Value = TimeEnd - TimeStart
Next
Application.ScreenUpdating = True
End Sub
Case 1 runs 5 TIMES FASTER than Case 2!
Even a Copy/Paste can be done without Activating and Selecting
CODE
Sub CopyAndPaste()
Range(Sheet1.Cells(1, 1), Sheet1.Cells(3, 3)).Copy _
Destination:=Sheet2.Cells(2, 5)
End Sub
The final technique is the use of the With...End With construct. The With statement allows you to perform a series of statements on a specified object without requalifying the name of the object. Running CompareMethods without With...End With, adds about 10% to the run time. Organize your code. Look for opportunities to nest With...End With within With...End With
Talk To Other Members