This is a useful little runner to compare two techniques to verify which is fastest.
Option Explicit Sub test() Dim tstart As Double, tend As Double Dim tdiff As Double, i As Long ' Method 2 tstart = Timer Range("B1").Select For i = 1 To 20000 ActiveCell.Offset(i - 1, 0).Value = i ^ 2 Next i tend = Timer tdiff = tend - tstart MsgBox "Method 2 takes " & tdiff & " seconds to complete" ' Method 1 Application.ScreenUpdating = False tstart = Timer ' Obtain initial time since midnight Sheets("Sheet1").Range("A1").Select For i = 1 To 20000 ActiveCell.Value = i ^ 2 ActiveCell.Offset(1, 0).Select Next i tend = Timer ' Obtain final time since midnight tdiff = tend - tstart ' Calculate the time elapsed to finish the loop MsgBox "Method 1 takes " & tdiff & " seconds to complete" End Sub |