How to use For Each Loop in VBA Excel?

In this blog explains how to use the For Each Statement to create a For Each loop in VBA with syntax and examples.

Description

For each is used to repeat a task whenever the condition is met. For each loop working with a collection of similar types of object ex. Workbook, Worksheet, Range, Cells, Pivot Table, Table, Chart.

Suppose we have to do lots of work in Excel i.e consolidate data from multiple worksheets in a single worksheet, import data into excel from SQL Server, import data into excel from MS Access, Creating Word Document, Import multiple files Name into Excel from folders and subfolders, Rename folder files, Insert object into excel, and lots of works to do then we use loop for work fast.

Syntax

How to create a FOR Each Loop in VBA using with FOR Each statement

        For each Object in Collections of Object

               VBA Statement

         Next Object

 

For Each Loop Example

How to get all the sheet names in excel using VBA?

Sub Worksheet_Name()

        Dim sh As Worksheet

        For Each sh in Worksheets

                MsgBox “Sheet Name is ” & sh.Name

        Next sh

End Sub

Follow Steps

  • Declare Object Variable for Worksheet

   Dim sh As Worksheet

  • Use For Each Loop

    For Each sh in Worksheets

    Worksheets is a collection of the Worksheet object.

  • Display Sheet Name into MsgBox

   MsgBox “Sheet Name is “ & sh.Name

  • Next Object

   Next sh

 

For Example, There is a workbook and the workbook have 5 sheets. Sheets name are Sheet1, Sheet2, Sheet3, Sheet4, and Sheet5. When we use this Procedure, MsgBox Display all Sheet Name.

  • Sheet Name is Sheet1
  • Sheet Name is Sheet2
  • Sheet Name is Sheet3
  • Sheet Name is Sheet4
  • Sheet Name is Sheet5

Note

For Each Loop repeat a task as per object numbers.

 

How to find all opened workbooks from VBA?

Sub Display_Workbooks()

    Dim wkb As Workbook

    For Each wkb In Workbooks

        MsgBox wkb.Name

    Next wkb

End Sub

Follow Steps

  • Declare Object Variable for Workbook

   Dim wkb as Workbook

  • Use For Each Loop

For Each wkb in Workbooks

    The Workbooks is a collection of all opened Workbook object

  • Display Sheet Name into MsgBox

   MsgBox “Workbook Name is “ & wkb.Name

  • Next Object

   Next wkb

 

For Example, There is 5 opened workbooks Book1, Book2, Book3, Book4, and Book5. When we use this Procedure, MsgBox Display all opened Book Name.

  • Workbook Name is Book1
  • Workbook Name is Book2
  • Workbook Name is Book3
  • Workbook Name is Book4
  • Workbook Name is Book5

Note

For Each Loop repeat a task as per object numbers.

 

How to use For Each Loop in VBA Excel?

 

How to use Nested FOR Each Loop in VBA?

As per need, we use multiple loops in our programming. We may use loop a within a loop.

how to use for each loop

Note

Each object variable must unique in Loop

 

Nested Loop Example

How to delete all comment for all worksheet using VBA?

We can delete all comments for all worksheet in a single workbook.

Sub Delete_Comment()

    Dim sh As Worksheet

    Dim Cell As Range

    For Each sh In ThisWorkbook.Sheets

        For Each Cell In sh.Cells.SpecialCells(xlCellTypeComments)

            Cell.Comment.Delete

        Next Cell

    Next sh

End Sub

 

Follow Steps

  • Declare Object Variable for Worksheet and Range

   Dim sh as Worksheet

   Dim Cell as Range

  • Use For Each Loop for Worksheet Object

    For Each sh in ThisWorkbook.Sheets

  • Use For Each Loop for Range Object

   For Each Cell in sh.Cells.SpecialCells(xlCellTypeComments)

  • Use Delete Method for Deleting Comment for all Worksheet

   Cell.Comment.Delete

  • Next Object for the inner loop

   Next Cell

  • Next Object for the outer loop

   Next sh

How to use For Each Loop in VBA Excel?

Leave a Reply

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