FOR EACH LOOP

Excel VBA For Each Loop – Complete Guide

Welcome to the complete Excel VBA For Each...Next Loop tutorial.

The For Each loop is one of the most useful VBA concepts for Excel automation. It allows you to process every item inside a collection or group of objects without manually managing a numeric counter.

In this lesson you will learn how to use For Each with:

  • Excel Cells
  • Ranges
  • Rows
  • Columns
  • Worksheets
  • Workbooks
  • Charts
  • Arrays
  • Collections
  • Nested loops
  • Exit For

For Each Loop Course Contents

  1. What is For Each?
  2. Why use For Each?
  3. For...Next vs For Each...Next
  4. Basic For Each Syntax
  5. Understanding the Loop Variable
  6. For Each with Range
  7. For Each with Cells
  8. For Each with Rows
  9. For Each with Columns
  10. For Each with Worksheets
  11. For Each with Workbooks
  12. For Each with Arrays
  13. For Each with Collections
  14. Using If inside For Each
  15. Using Exit For
  16. Nested For Each
  17. Practical Excel Projects
  18. Common Mistakes
  19. Performance Tips
  20. Practice Exercises
  21. Practical Quiz

1. What is For Each Loop?

A For Each loop repeats a block of VBA code for every item in a collection or group.

For example, suppose you have 10 cells: 

A1
A2
A3
...
A10

Instead of manually processing each cell, you can write: 

Sub Example()

    Dim cell As Range

    For Each cell In Range("A1:A10")

        Debug.Print cell.Value

    Next cell

End Sub

VBA automatically moves from one cell to the next.

2. Why Use For Each?

For Each is particularly useful when working with Excel objects.

For example:

  • Every cell in a range
  • Every worksheet in a workbook
  • Every workbook in a collection
  • Every row in a range
  • Every column in a range
  • Every item in a Collection
  • Every object in an Excel collection
Simple rule:

If your requirement is:

"Do something for every item"

then For Each should immediately come to mind.

3. For...Next vs For Each...Next

For...Next For Each...Next
Uses a numeric counter Uses an object/item variable
Good for numbers Good for collections and objects
Example: 1 to 100 Example: every worksheet
You control start/end values VBA moves through each item

For...Next Example

Dim i As Long

For i = 1 To 10

    Debug.Print i

Next i

For Each Example

Dim ws As Worksheet

For Each ws In Worksheets

    Debug.Print ws.Name

Next ws

4. Basic For Each Syntax

The basic syntax is:

For Each variable In collection

    'Code

Next variable

Example:

Sub BasicForEach()

    Dim cell As Range

    For Each cell In Range("A1:A5")

        Debug.Print cell.Value

    Next cell

End Sub

Breaking Down the Code

Code Meaning
Dim cell As Range Creates a Range object variable
For Each Starts the loop
cell Represents the current item
In Specifies the collection
Range("A1:A5") Collection of cells
Next cell Moves to the next cell

5. Understanding the Loop Variable

The variable after For Each represents the current object being processed. 

Dim cell As Range

For Each cell In Range("A1:A5")

    Debug.Print cell.Address

Next cell

During the first iteration, cell represents A1.

During the second iteration, cell represents A2.

The process continues until the last cell.

6. For Each with Range

This is probably the most common Excel VBA use of For Each. 

Sub RangeExample()

    Dim cell As Range

    For Each cell In Range("A1:A10")

        Debug.Print cell.Value

    Next cell

End Sub

Read Cell Address and Value

Sub CellDetails()

    Dim cell As Range

    For Each cell In Range("A1:A10")

        Debug.Print cell.Address
        Debug.Print cell.Value

    Next cell

End Sub

7. For Each with Cells

You can process cells within a larger range. 

Sub CheckCells()

    Dim cell As Range

    For Each cell In Range("A1:C10")

        Debug.Print cell.Address
        Debug.Print cell.Value

    Next cell

End Sub

This processes every cell from A1 through C10.

8. For Each with Rows

You can also loop through rows. 

Sub LoopRows()

    Dim rw As Range

    For Each rw In Range("A1:C10").Rows

        Debug.Print rw.Row

    Next rw

End Sub

Process Every Row

Sub FormatRows()

    Dim rw As Range

    For Each rw In Range("A1:C10").Rows

        rw.Font.Bold = True

    Next rw

End Sub

9. For Each with Columns

Sub LoopColumns()

    Dim col As Range

    For Each col In Range("A1:D10").Columns

        Debug.Print col.Column

    Next col

End Sub

Format Every Column

Sub FormatColumns()

    Dim col As Range

    For Each col In Range("A1:D10").Columns

        col.EntireColumn.AutoFit

    Next col

End Sub

10. For Each with Worksheets

This is one of the most important professional VBA applications. 

Sub LoopWorksheets()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        Debug.Print ws.Name

    Next ws

End Sub

Change Tab Color

Sub ChangeSheetColor()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        ws.Tab.Color = vbGreen

    Next ws

End Sub

Write Message into Every Worksheet

Sub AddMessage()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        ws.Range("A1").Value = "Created by CHIRAGCODER"

    Next ws

End Sub

11. For Each with Workbooks

Excel also maintains a collection of currently open workbooks. 

Sub LoopWorkbooks()

    Dim wb As Workbook

    For Each wb In Application.Workbooks

        Debug.Print wb.Name

    Next wb

End Sub

Display Workbook Names

Sub WorkbookNames()

    Dim wb As Workbook

    For Each wb In Workbooks

        MsgBox wb.Name

    Next wb

End Sub

12. For Each with Arrays

For Each can also be used with arrays. 

Sub ArrayExample()

    Dim arr As Variant
    Dim item As Variant

    arr = Array("Excel", "VBA", "SQL", "Python")

    For Each item In arr

        Debug.Print item

    Next item

End Sub

Output:

Excel
VBA
SQL
Python
Important:

When you need the array index, a normal For...Next loop is often more appropriate.

13. For Each with Collection

VBA Collections can also be processed using For Each. 

Sub CollectionExample()

    Dim myCollection As Collection
    Dim item As Variant

    Set myCollection = New Collection

    myCollection.Add "Excel"
    myCollection.Add "VBA"
    myCollection.Add "SQL"

    For Each item In myCollection

        Debug.Print item

    Next item

End Sub

14. If Statement Inside For Each

One of the most powerful combinations is:

For Each + If 

Sub FindHighValues()

    Dim cell As Range

    For Each cell In Range("A1:A100")

        If cell.Value > 1000 Then

            cell.Font.Bold = True

        End If

    Next cell

End Sub

Highlight Negative Values

Sub HighlightNegative()

    Dim cell As Range

    For Each cell In Range("A1:A100")

        If cell.Value < 0 Then

            cell.Interior.Color = vbRed

        End If

    Next cell

End Sub

15. Check for Blank Cells

Sub FindBlankCells()

    Dim cell As Range

    For Each cell In Range("A2:A500")

        If Trim(cell.Value) = "" Then

            cell.Interior.Color = vbYellow

        End If

    Next cell

End Sub

16. Exit For

You can stop a For Each loop using Exit For. 

Sub FindCustomer()

    Dim cell As Range

    For Each cell In Range("A2:A1000")

        If cell.Value = "Rahul" Then

            MsgBox "Customer Found"

            Exit For

        End If

    Next cell

End Sub

Once the required item is found, continuing through the remaining cells is unnecessary, so Exit For terminates the loop.

17. Nested For Each

You can place one For Each loop inside another.

For example, process every worksheet and every cell in a range on each worksheet. 

Sub NestedForEach()

    Dim ws As Worksheet
    Dim cell As Range

    For Each ws In ThisWorkbook.Worksheets

        For Each cell In ws.Range("A1:A10")

            Debug.Print ws.Name, cell.Address, cell.Value

        Next cell

    Next ws

End Sub
Think about the process:

First VBA selects one worksheet.

Then it processes every cell in A1:A10.

After finishing those cells, it moves to the next worksheet.

18. Practical Project – Find Blank Cells

Sub FindAndHighlightBlankCells()

    Dim cell As Range

    For Each cell In Range("A2:A100")

        If IsEmpty(cell) Then

            cell.Interior.Color = vbYellow

        End If

    Next cell

    MsgBox "Blank cell checking completed."

End Sub

19. Practical Project – Find Duplicate Values

Suppose customer IDs are stored in column A. You can use a dictionary later for a high-performance solution, but For Each is useful for learning the looping concept. 

Sub CheckDuplicateExample()

    Dim cell As Range

    For Each cell In Range("A2:A100")

        If WorksheetFunction.CountIf( _
            Range("A2:A100"), cell.Value) > 1 Then

            cell.Interior.Color = vbYellow

        End If

    Next cell

End Sub

20. Practical Project – Format All Worksheets

Sub FormatAllSheets()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        With ws.Range("A1")

            .Font.Bold = True
            .Font.Size = 16

        End With

        ws.Columns.AutoFit

    Next ws

End Sub

21. Practical Project – Find a Value in Every Sheet

Sub SearchAllSheets()

    Dim ws As Worksheet
    Dim cell As Range

    For Each ws In ThisWorkbook.Worksheets

        For Each cell In ws.UsedRange

            If cell.Value = "Total" Then

                Debug.Print ws.Name, cell.Address

            End If

        Next cell

    Next ws

End Sub

22. Practical Project – Delete Empty Rows

Important:

When deleting rows while looping, you need to be careful because the collection/range changes as rows are removed. A reverse numeric loop is often safer for this type of operation.

For example: 

Sub DeleteEmptyRows()

    Dim i As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = lastRow To 2 Step -1

        If Trim(Cells(i, 1).Value) = "" Then

            Rows(i).Delete

        End If

    Next i

End Sub

This is an important professional lesson: For Each is not always the best loop for every task.

23. Common For Each Mistakes

Mistake 1 – Wrong Variable Type

Incorrect:

Dim cell As Long

For Each cell In Range("A1:A10")

Next cell

A cell is an Excel Range object, so normally use: 

Dim cell As Range

Mistake 2 – Forgetting Next

For Each cell In Range("A1:A10")

    Debug.Print cell.Value

'Missing Next cell

Mistake 3 – Using For Each When You Need an Index

Suppose you specifically need row numbers or array indexes. A normal For...Next loop may be easier. 

Dim i As Long

For i = 1 To 100

    Debug.Print i

Next i

Mistake 4 – Modifying the Collection

Be careful when deleting or changing items while using For Each. The collection can change during iteration.

24. For Each Performance

For Each is convenient and readable, but performance depends on what your loop does.

For large Excel datasets, repeatedly accessing worksheet cells can be slower than loading the data into an array and processing the array in memory.

Professional VBA developers should eventually learn:

  • Arrays
  • Variant arrays
  • Dictionary
  • Range.Value
  • Bulk operations
  • ScreenUpdating
  • Calculation settings

25. For Each Cheat Sheet

Requirement Example
Every cell For Each cell In Range(...)
Every row For Each rw In Range(...).Rows
Every column For Each col In Range(...).Columns
Every worksheet For Each ws In Worksheets
Every workbook For Each wb In Workbooks
Every array item For Each item In arr
Every collection item For Each item In myCollection
Stop loop Exit For

26. Practical Exercises

Beginner

Exercise 1: Loop through A1:A10 and print every cell value.

Exercise 2: Loop through A1:A100 and highlight values greater than 500.

Exercise 3: Loop through A1:A100 and highlight blank cells.

Exercise 4: Loop through all worksheets and print their names.

Intermediate

Exercise 5: Loop through every worksheet and write the worksheet name into A1.

Exercise 6: Loop through every cell in A1:C100 and find cells containing "Pending".

Exercise 7: Loop through every worksheet and count how many cells contain the word "Total".

Exercise 8: Create an array containing 10 names and print each name using For Each.

Advanced

Exercise 9: Loop through every worksheet and every cell in UsedRange. Find cells containing the word "Error".

Exercise 10: Loop through every worksheet and find the first occurrence of "Grand Total". Stop searching that worksheet after finding it.

27. For Each Practical Quiz

Question 1:
What is the primary purpose of a For Each loop?

A. Repeat code for each item in a collection

B. Create a new workbook

C. Delete a worksheet

D. Change the VBA editor

Answer: A — For Each processes each item in a collection or group.

Question 2:
Which declaration is appropriate when looping through Excel cells?

A. Dim cell As Long

B. Dim cell As Range

C. Dim cell As Integer

D. Dim cell As Boolean

Answer: B — An Excel cell is represented by a Range object.

Question 3:
Which code loops through every worksheet?

A. For Each ws In Worksheets

B. For Each ws In Cells

C. For Each ws In Rows

D. For Each ws In Columns

Answer: A — Worksheets is the collection containing the worksheets.

Question 4:
What does the variable represent in this code? 

For Each cell In Range("A1:A10")

    Debug.Print cell.Value

Next cell

A. The entire workbook

B. The current cell

C. The current worksheet

D. The row number only

Answer: B — cell represents the current Range item being processed.

Question 5:
Which statement immediately stops a For Each loop?

A. Stop Loop

B. Exit Loop

C. Exit For

D. Break For

Answer: C — Exit For terminates the current For loop.

Question 6:
What does this code process? 

For Each cell In Range("A1:C10")
    Debug.Print cell.Value
Next cell

A. Only A1

B. Only column A

C. Every cell in A1:C10

D. Every worksheet

Answer: C — The loop enumerates every cell in the specified range.

Question 7:
Which statement is true about For Each and array indexes?

A. For Each always provides the array index automatically

B. For Each provides the current item, not a separate index variable

C. For Each cannot process arrays

D. For Each only works with worksheets

Answer: B — For Each gives you the current item; if you need an explicit index, a normal For...Next loop may be more suitable.

Question 8:
What does the following code do? 

For Each ws In Worksheets

    ws.Range("A1").Value = "Hello"

Next ws

A. Writes Hello into A1 of every worksheet

B. Writes Hello only into the active worksheet

C. Creates a worksheet named Hello

D. Deletes A1 from every worksheet

Answer: A — Each worksheet is processed and its A1 cell receives the value.

Question 9:
What is a nested For Each loop?

A. A For Each loop inside another loop

B. A loop without Next

C. A loop that runs backward

D. A loop that only processes numbers

Answer: A — A nested loop places one loop inside another.

Question 10:
Which situation is generally better suited to a numeric For...Next loop?

A. Processing every worksheet

B. Processing every cell in a range

C. Processing rows from a known starting number to a known ending number

D. Processing every item in a Collection

Answer: C — A numeric For...Next loop is convenient when you need explicit counter values.

28. For Each – Final Summary

Concept Example
Basic For Each For Each cell In Range(...)
Cells For Each cell In Range("A1:C10")
Rows For Each rw In Range(...).Rows
Columns For Each col In Range(...).Columns
Worksheets For Each ws In Worksheets
Workbooks For Each wb In Workbooks
Arrays For Each item In arr
Collections For Each item In myCollection
Stop Loop Exit For
Nested Loop For Each inside For Each

29. Key Point to Remember

For...Next: 

For i = 1 To 100

Think: "I want to repeat something using a counter."

For Each: 

For Each cell In Range("A1:A100")

Think: "I want to process every item in this collection."

30. Next Topic

After completing For Each, the recommended next lesson is:

VBA Do Loop – Complete Guide

  • Do While...Loop
  • Do Until...Loop
  • Do...Loop While
  • Do...Loop Until
  • Exit Do
  • Infinite loops
  • Practical Excel examples
  • Exercises
  • Advanced quiz

Post a Comment

0 Comments