VBA For Each Next Loop

What is the For Each Next loop in VBA?

In this article you will learn what the For Each Next loop is and how to use it in Excel VBA. The For Each Next loop is an extension of the VBA For loop . The difference between the two types of loops is that For Each we can apply to a certain group of elements, for example to:

  • all cells in the range
  • all sheets
  • all pivot tables, etc.

The assembly of the VBA For Each Next loop is as follows:

element – element to which we refer in the group, ie.: every cell, every sheet, etc.

group – a group of elements eg all sheets, cells in the range, pivot tables, etc.

statements – commands that will cover all elements in a group. For example, all cells are colored yellow, all sheets are hidden, etc.

The VBA loop for each next works on elements. The most commonly used elements in the loop are:

  • Worksheet in the Worksheets group  – sheet
  • Pivottable in the Pivottables group – pivot tables
  • element in the Range group (“X: X”) – cells in the range group

Examples of VBA For Each Next

Example 1 : Below is the simplest example of the Each loop. The example is to enter any text into the A1 cell in any sheet.

Example description : As you can see, the element in the loop is the sheet defined at the beginning of the code as Worksheet. The loop passes after each sheet in turn and inserts the text in cell A1. The group of objects in the loop are the sheets in the active workbook labeled  ActiveWorkbook.Worksheets .

Example 2 : Let’s create a For Each Next loop, which searches for empty cells in the B1: c10 cell range. Then select the inside of empty cells with yellow color.

Example description : As you can see, the cell marked as an item in the range is checked for an empty value. If the For Each Next loop encounters an empty value, it lights up the cell in yellow. You can read more about VBA cell formatting in this part of the course: VBA Formatting cells and fonts .

Some other examples of the VBA For Each Next loop:

  • Display information about PivotTables
  • Discovering all hidden sheets in VBA
  • Refreshing all VBA pivot tables

The article is part of the Excel vba course. The whole course can be found at this link:  VBA course . Below is the file with the program code:

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: