For vs For Each – which is faster?

Some time ago, when I was learning about looping I read that “You should avoid For Each loop when it is not necessary” or something like that. From that time I was trying to use only For loops. You can imagine my surprise when some guy with over 50k reputation points on StackOverflow said to me encouraging to use For Each, because it is faster! So which is faster – For vs For Each?

So why not check it? 🙂

As always I wrote a simply code to found out if this is true.

Option Explicit

Sub main()

Dim clock As Double
Dim i As Long
Dim colA As Range, cel As Range

clock = Timer

With ThisWorkbook.Sheets(1)

    For i = 1 To x

        .Cells(i, 1) = 1
    
    Next
    
    Debug.Print Round(Timer - clock, 3)
    
    clock = 0
    clock = Timer
    Set colA = .Range(.Cells(1, 1), .Cells(x, 1))
    
    For Each cel In colA
    
        cel = 1
        
    Next
    
    Debug.Print Round(Timer - clock, 3)
    
    'MsgBox "Done"
    
End With

End Sub

Where x stand for rows (see table).

Results

Rows (x)	For	For Each
100	0,000	0,000
1000	0,020	0,012
10000	0,160	0,113
100000	1,855	1,328
1000000	19,551	13,113

We can see from the table, that all in all he was right! In that case – looping through range cells – is faster. Until 100 000 rows we can’t really notice any significant time difference between loops, but above that amount, with 1 000 000 rows, it is over 6 seconds. We can easily call it: a huge gap!

Numbers not lying, to be honest For Each is faster in every case than simple For. Keep in mind, that it is just simple looping and filling cells with number 1. In more complicated cases the time difference can be much, much bigger.

However, remember about that You can’t or it is more complicated to use only one type of loop. In some cases will be easier and faster to use For Each loop and in some more accurate method for further functions will be For loop. Sometimes bigger simplicity in code is more important than their speed. Everything depends, especially when the difference is not that big.

Of course I am not telling You to take my word for it, make your own comparison For vs For each and check out which is faster for yourself : )