Skip to content

VBA For Each Examples (Quick Reference)

Associated Files Download Links

This tutorial will show you examples of using the For Each Loop in VBA. Click here to learn more about loops in general.

For Each Loop

The For Each Loop allows you to loop through each object in a collection:

  • All cells in a range
  • All worksheets in a workbook
  • All open workbooks
  • All shapes in a worksheet
  • All items in an array
  • and more!

For Each: Basic Examples

These examples will demonstrate how to set up For Each loops to loop through different types of objects.

Loop Through Cells

This procedure will loop through each cell in range A1:A10, setting the cell to it’s right equal to itself.

Loop Through Sheets

This procedure will loop through each sheet in a Workbook, unhiding each sheet.

Loop Through Workbooks

This procedure will loop through each workbook, closing each one.

Loop Through Shapes

This procedure will loop through each shape in Sheet1, deleting each one.

Loop Through Charts

This procedure will loop through each Chart in Sheet1, deleting each one.

Loop Through PivotTables

This procedure will loop through each PivotTable in Sheet1, clearing each one

Loop Through Tables

This procedure will loop through each Table in Sheet1, deleting each one.

Loop Through Items in Array

This procedure will loop through each item in an Array, display each value in a msgbox,

Loop Through Numbers

This procedure will loop through each number in an Array, display each value in a msgbox,

For Each Loop Builder

The examples in this article were built with the Loop Builder in our VBA Add-in: AutoMacro.

 

vba loop builder

The Loop Builder makes it very easy to generate code to loop through objects.  AutoMacro also contains many other Code Generators, an extensive Code Library, and powerful Coding Tools.

For Each – If

You can also use If Statements within Loops to test if objects meet certain criteria, only performing actions on those objects that meet the criteria.  Here is an example of looping through each cell in a range:

For Each Cell in Range – If

vba for each cell in range

 

For Each Common Examples

Close All Workbooks

This procedure will close all open workbooks, saving changes.

Hide All Sheets

This procedure will hide all worksheets.

Unhide All Sheets

This procedure will unhide all worksheets.

Protect All Sheets

This procedure will protect all worksheets.

Unprotect All Sheets

This procedure will unprotect all worksheets.

Delete All Shapes On All Worksheets

This procedure will delete all shapes in a workbook.

Refresh All PivotTables

This procedure will refresh all PivotTables on a sheet.

 

You may also like some of this related content...

Advertisements
Automate Excel
Left Menu Icon