4 Examples to Understand VBA For Each Loop [Cells, Arrays]

The For..Each is a loop type in VBA. This loop is used to execute one or more statements for arrays or collections

What is For Each loop in VBA

  • The For..Each is a loop type in VBA
  • This loop is used to execute one or more statements for arrays or collections
  • For each element of the collection/array, the statements inside the loop execute
  • The collection example can be a range of cells in Excel

An example of For Each with cell range

Let us start our example with the range of cells in the For Each loop. First, have a look at our sample sheet that we will use for our example:


We will create a range of six cells (B2 to B7) and use them in the For Each.

Inside the For Each, we will get the value of each cell and concatenate the values in a String variable.

Finally, we will display a MsgBox with the values of all cells.

VBA code with For Each loop



Syntax of For Each loop

So, based on our above example, you can notice the syntax of using For..Each .. Next loop:

An example of using For Each with an array

For this example, we have created an array of programming languages. In the For..Each loop, that iterates through each element of the array, we will display a MsgBox with the current array element value.

This should make your For Each concept clearer as the MsgBox statement is written once and you can see it executes for each item separately i.e. message box appears four times as you execute this code:

VBA code:

Iterate through an array partially

Rather than iterating through each element of the array as using For Each loop, you may go through the array partially.

For that, you may use the If statement and check the element value and exit the for loop if the condition is met.

The example below shows how the If condition is used in our array:

The code:



You can see, only two languages are displayed.

How to omit only the current iteration solution

Though the continue statement is not available in VBA, as in other programming languages. We can omit the current element (without executing any code) by using another way (which is the purpose of continue).

So, again consider our array that has four languages.

In the example below, we will omit “C++” and display the other three languages (elements).

VBA code:



You can see, though C++ is our third element, we did not display it in the message box.