For Each...Next

A For Each...Next loop is similar to a For...Next loop, except that it repeats a group of statements for each element in a collection of objects or in an array, instead of repeating the statements a specified number of times. This is especially useful if you don't know how many elements are in a collection, or if the contents of the collection might change as your procedure runs.

For Each element In group
statements
Next element

When Visual Basic runs a For Each...Next loop, it follows these steps:

1. It defines element as naming the first element in group (provided that there is at least one element).

2. It runs the statements.

3. It tests to see whether element is the last element in group. If so, Visual Basic exits the loop.

4. It defines element as naming the next element in group.

5. It repeats steps 2 through 4.

Keep the following restrictions in mind when using the For Each...Next statement:

• For collections, element can only be a Variant variable, a generic Object variable, or any specific OLE Automation object variable. (For information about OLE Automation objects, see Chapter 10, "Communicating with Other Applications.") For arrays, element can only be a Variant variable.
• You cannot use the For Each...Next statement with an array of user-defined types because a Variant variable cannot contain a user-defined type. For more information about data types, see Chapter 2, "Variables, Constants, and Data Types."

The following example examines each cell in the current region for cell A1 on the worksheet named "sheet3" and deletes the cell if its value is less than -1.

For Each c In Worksheets("sheet3").Range("a1").CurrentRegion.Cells
    If c.Value < -1 Then c.Delete
Next c

For more information about collections and more examples of the For Each...Next statement, see Chapter 4, "Objects and Collections."

Note

The variable name after the Next statement is optional, but it can make your code easier to read, especially if you have several nested For loops.