VBA Ranges - Looping with For Each

August 23, 2017

In our last discussion about VBA Ranges, we learned how to get and set cells. In this post, I’d like to build on top of that knowledge by showing you how to loop through cells in VBA. There are a few ways to loop through cells with VBA, but in this post, we’ll focus on the For Each looping mechanism. We’ll cover other ways to loop through cells in future posts. Let’s get started.

For Each Syntax

The syntax of For Each is the following:

For Each [object] In [collection]
  [statements]
  [Exit For]
Next [object]
Item Description
object This is an object that will contain a reference to the current item within the collection. This is what you’ll be working with within the For Each loop. Also, specifying this in the Next statement is optional (but I recommend doing it to make nested loops easier to read).
collection This is an object of a Collection data type. This object must contain one or more items to loop through in order to enter the For Each loop.
Exit For This is another optional statement that, if executed, will exit the For Each loop even if there are more objects to process in the collection.

Does this article help you? If so, please consider supporting me with a coffee ☕️

Buy Me a Coffee at ko-fi.com

For Each Basic Example

Here’s a simple For Each example:

Option Explicit

Public Sub UpdateTextUsingForEach()
    Dim cell As Excel.Range

    For Each cell In Selection
        cell.Value = cell.Value & " extra text"
    Next cell
End Sub

In this example, we simply loop through all of the cells in the current Selection and add some extra text to it. Here is this code in action:

For Each - Basic Example - In Action

For Each Example Skipping some Cells

Let’s say that we wanted to skip certain cells as we loop through them. For example, if we want to only add some extra text to cells that are not blank, we need to add a condition to check for that and then skip that cell and move on to the next.

In other languages, we could use the Continue keyword. Unfortunately, VBA doesn’t have that option, but we can get around that with using a GoTo statement.

Option Explicit

Public Sub UpdateTextUsingForEach()
    Dim cell As Excel.Range

    For Each cell In Selection
        If (cell.Value = "") Then
          GoTo Continue
        End If

        cell.Value = cell.Value & " extra text"
Continue:
    Next cell
End Sub

Here, we check if the cell’s value is blank and if so, we go to the Continue label, which is at the end of the For Each loop.

Here it is in action:

For Each with skipping logic

Once the code reached a cell that was blank, you see the GoTo Continue line was executed and the rest of the For Each loop was skipped.

For Each Example with Exiting the Loop

Finally, let’s say that if you find a blank cell, that you’d like to completely stop the loop. You may need this if the user were to select a bunch of extra cells that were blank. This could help save you execution time and speed up the code as well.

Here’s an example of what that code would look like:

Option Explicit

Public Sub UpdateTextUsingForEach()
    Dim cell As Excel.Range

    For Each cell In Selection
        If (cell.Value = "") Then
            Exit For
        End If

        cell.Value = cell.Value & " extra text"
    Next cell
End Sub

And here’s what that would look like in action:

For Each with Exit For

Once we hit a cell that had blank text, the rest of the cells were skipped because we exited the loop.

By the way, don’t mind that weird extra text “d Sub” in the image. That’s just Excel VBA for Mac acting all wonky.

Order of Operation

One last thing I’d like to talk about is the order that the For Each goes through if you have cells selected that span multiple rows and columns. Going back to our original code, what order do you think Excel would go in if we selected cells A1:B3? Would it go through column A first, then move on to column B? Or would it go through the cells row by row?

Let’s take a look:

For Each Order of Operation

As you can see, it goes row by row, starting at A1, then B1, A2, B2, A3, and finally B3. This might not mean much to you now, but you never know when you will need to process the cells in a different order. For example, what if you wanted to start from the bottom and work your way up? For that, we’ll need to use a different looping construct which we’ll talk about in another post.


Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).

Buy Me a Coffee at ko-fi.com

Written by Joseph who loves teaching about Excel.

© 2021, Spreadsheets Made Easy