For Each Loops in Excel VBA

For Each loops are normally used with collections and arrays (you'll learn about Arrays in a later lesson). This just means that you have one or more items to cycle through. It's still a For loop, though. But this time the word "Each" follows the word "For", with a space between the two. The Each part means "Each individual item in the collection or array". The structure of a For Each loop looks like this:

For Each variable_name In collection_name

Next variable_name

So you start with For Each. You then need a variable name. This can be almost anything you want, just like normal variables. VBA will store individual items into this variable. After your variable name you need the word "In". You then follow that with the name of the collection or array you're trying to access. The final line is the same as for normal For loops - Next variable_name.

As the code for the For Each loop, though, you do something with Each item stored in that variable_name. Let's see an example.

Suppose you have the following spreadsheet:

Excel spreadsheet showing values in cells

You want to remove the hyphens from each cell in the A column. To do this, you can use a For Each loop. The collection is the Range of cells A2 to A6. Each item is a cell in that range. The For Each loop would then start like this:

For Each MyCell In Range("A2:A6")

The variable name that will store individual cells from the range is called MyCell. The collection after the word "In" is Range("A2:A6").

The end of the loop would then be:

Next MyCell

As the code for the For Each loop we can do this:

MyCell.Value = Replace(MyCell.Value, "-", "")

The variable called MyCell is now a cell in its own right. This means that MyCell will have a Value property - the contents of the cell. The first time round the loop this will be the contents of cell A2. After an equal sign, we're using the Replace function. We're replacing the hyphen with no space at all (this is done with two double quotes).

So with just a couple of lines of code we can cycle through all the cells in the A column, doing whatever we want with the contents of each cell. A pretty powerful technique, hey!

 

In the next lesson, we'll discuss the Cells property, which is useful when using loops.

 

Lots more free online course here on our main Home and Learn site