In Excel VBA, loops are one of the most powerful features for automating repetitive tasks. Among them, the “For Each” statement stands out for its simplicity, readability, and flexibility.

Unlike traditional index-based loops such as For i = 1 To n, the For Each statement allows you to iterate through a collection of objects — such as cells, worksheets, workbooks, charts, or even shapes — without worrying about numerical indexes.

In this comprehensive guide, you’ll learn how to use the For Each statement effectively, understand how it works behind the scenes, and discover real-world applications that will help you automate Excel like a professional VBA developer.

---

✅ What Is the For Each Statement?

・Definition and purpose

The For Each statement in VBA is used to loop through each item (object or element) in a collection or array.

Its general syntax is:

For Each element In collection
' Code to execute
Next element

• element — a variable representing the current item in the loop
• collection — a group of objects (e.g., Worksheets, Cells, Shapes)

The loop runs once for each item in the collection, automatically moving to the next element until the last one is processed.

---

・Why use For Each?

The For Each loop is ideal when:

• You need to process all elements in a collection (like all worksheets or all cells in a range).
• The number of items may change dynamically.
• You want readable and maintainable code without worrying about index boundaries.

It’s especially useful in object-based programming — a core concept in Excel VBA.

---

✅ Basic Example: Looping Through Cells in a Range

・Example: Simple For Each loop

Sub ExampleForEach()
Dim c As Range
For Each c In Range("A1:A5")
c.Value = "Sample"
Next c
End Sub

✅ The macro writes “Sample” into each cell from A1 to A5.
✅ You don’t need to count rows or columns — VBA automatically iterates through all cells in the specified range.

---

・Example: Highlight cells with values

Sub HighlightNonEmptyCells()
Dim cell As Range
For Each cell In Range("B1:B10")
If Not IsEmpty(cell.Value) Then
cell.Interior.Color = vbYellow
End If
Next cell
End Sub

✅ Highlights all non-empty cells in yellow.
✅ Great for visually identifying filled data areas.

---

✅ Using For Each with Worksheets

・Loop through all worksheets in a workbook

Sub ListAllSheetNames()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Next ws
End Sub

✅ Prints the names of all sheets in the Immediate Window.
✅ Perfect for auditing or managing multiple sheets dynamically.

---

・Activate each sheet one by one

Sub ActivateSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
MsgBox "Now viewing: " & ws.Name
Next ws
End Sub

✅ Moves through each worksheet sequentially.
✅ Useful when reviewing or updating several sheets manually.

---

✅ Working with Workbooks Using For Each

If multiple workbooks are open, you can iterate through them all:

Sub CloseOtherWorkbooks()
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close SaveChanges:=False
End If
Next wb
End Sub

✅ Closes all workbooks except the one running the macro.
✅ A common technique for cleaning up open files in automated processes.

---

✅ Using For Each with Charts and Shapes

・Example: Modify all charts in a sheet

Sub UpdateAllCharts()
Dim ch As ChartObject
For Each ch In ActiveSheet.ChartObjects
ch.Chart.ChartTitle.Text = "Updated Report"
Next ch
End Sub

✅ Automatically updates the chart title for all charts on the active sheet.

---

・Example: Change color of all shapes

Sub ColorAllShapes()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Fill.ForeColor.RGB = RGB(0, 176, 240)
Next shp
End Sub

✅ Applies the same color to all shapes on the sheet.
✅ A great way to standardize presentation formatting.

---

✅ Looping Through Rows and Columns

・Example: Loop through entire rows

Sub ProcessRows()
Dim r As Range
For Each r In Range("A1:A10").EntireRow
r.Cells(1, 1).Value = "Row " & r.Row
Next r
End Sub

✅ Writes the row number in the first cell of each row in A1:A10.

---

・Example: Loop through columns

Sub LabelColumns()
Dim c As Range
For Each c In Range("A1:E1").EntireColumn
c.Cells(1, 1).Value = "Col " & c.Column
Next c
End Sub

✅ Adds “Col 1”, “Col 2”, etc., at the top of each column in the range.

---

✅ Using For Each with Arrays

While For Each is mainly used for object collections, it can also iterate through arrays — especially string or variant arrays.

Sub ArrayExample()
Dim items As Variant
Dim i As Variant
items = Array("North", "South", "East", "West")
For Each i In items
Debug.Print i
Next i
End Sub

✅ Loops through all array elements.
✅ Easy to manage dynamic data sets without indexes.

---

✅ Applying Conditional Logic in For Each Loops

・Example: Highlight specific text

Sub HighlightErrorCells()
Dim cell As Range
For Each cell In Range("A1:A20")
If cell.Value = "Error" Then
cell.Interior.Color = vbRed
End If
Next cell
End Sub

✅ Highlights only the cells containing “Error”.
✅ A practical example for data validation macros.

---

・Example: Skip blank cells safely

Sub SkipBlanks()
Dim cell As Range
For Each cell In Range("B1:B20")
If Not IsEmpty(cell.Value) Then
cell.Font.Bold = True
End If
Next cell
End Sub

✅ Avoids unnecessary processing for blank cells.
✅ Clean, efficient, and safe coding.

---

✅ Combining For Each with If Statements and Exit

・Break the loop early using Exit For

Sub StopAtTarget()
Dim cell As Range
For Each cell In Range("A1:A100")
If cell.Value = "STOP" Then
MsgBox "Target found in cell " & cell.Address
Exit For
End If
Next cell
End Sub

✅ Terminates the loop once a specific condition is met.
✅ Prevents unnecessary iterations, improving performance.

---

✅ Advanced Example: Copy Filtered Data Between Sheets

Sub CopyFilteredData()
Dim cell As Range
For Each cell In Sheets("Data").Range("A2:A50")
If IsNumeric(cell.Value) And cell.Value >= 80 Then
Sheets("Results").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = cell.Value
End If
Next cell
End Sub

✅ Copies all values ≥ 80 from “Data” to “Results.”
✅ A practical example for reporting and score filtering.

---

✅ Nested For Each Loops

You can nest For Each loops to handle multi-level objects — for example, looping through worksheets, then cells within each sheet.

Sub NestedForEachExample()
Dim ws As Worksheet
Dim cell As Range
For Each ws In ThisWorkbook.Worksheets
For Each cell In ws.Range("A1:A3")
cell.Value = ws.Name & " - " & cell.Row
Next cell
Next ws
End Sub

✅ Adds the sheet name and row number into cells A1–A3 of each sheet.
✅ Demonstrates how powerful For Each can be for workbook-wide automation.

---

✅ Working with Object Properties During Loops

・Example: Check for hidden sheets

Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Visible = xlSheetHidden Then
ws.Visible = xlSheetVisible
End If
Next ws
End Sub

✅ Automatically unhides all hidden sheets in the workbook.
✅ Useful for admin or debugging purposes.

---

・Example: Delete empty charts automatically

Sub DeleteEmptyCharts()
Dim ch As ChartObject
For Each ch In ActiveSheet.ChartObjects
If ch.Chart.SeriesCollection.Count = 0 Then
ch.Delete
End If
Next ch
End Sub

✅ Cleans up unused charts quickly.
✅ A professional technique for managing report templates.

---

✅ Common Mistakes and How to Avoid Them

Mistake	Cause	Solution
Using the wrong variable type	Declaring element as Variant instead of correct object	Use Dim ws As Worksheet, Dim c As Range, etc.
Modifying the collection during the loop	Adding or deleting items while looping	Avoid structural changes while iterating
Forgetting to qualify ranges	Implicitly referring to ActiveSheet	Always use ThisWorkbook.Sheets("Name").Range(...)
Slow execution on large data	Looping cell-by-cell unnecessarily	Use arrays or batch operations where possible

---

✅ Performance Optimization Tips

・Disable screen updates

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'--- Your For Each loop code ---
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

✅ Prevents flickering and speeds up large operations dramatically.

---

・Use With blocks for efficiency

With Sheets("Report").Range("A1:A100")
Dim cell As Range
For Each cell In .Cells
cell.Value = cell.Value * 2
Next cell
End With

✅ Cleaner, faster, and reduces repetitive references.

---

✅ Real-World Applications of For Each

Scenario	Example	Benefit
Data cleaning	Remove blank rows or format text	Automates repetitive data prep
Reporting	Highlight KPIs or update charts	Makes reports dynamic
Sheet management	Rename, hide, or delete sheets	Simplifies workbook structure
Quality control	Check for errors or missing data	Ensures accuracy
Integration with RPA tools	UiPath + VBA loops	Extends automation across platforms

The For Each statement is essential for anyone who wants to scale Excel automation efficiently and safely.

---

✅ Combining For Each with RPA and Power Automate

When integrating with tools like UiPath or Power Automate Desktop, For Each loops are perfect for handling dynamic Excel elements.

For example, an RPA robot can trigger a VBA macro that:

• Iterates through all sheets
• Processes only visible ones
• Exports reports automatically

Sub ExportVisibleSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Visible = xlSheetVisible Then
ws.ExportAsFixedFormat xlTypePDF, "C:\Reports\" & ws.Name & ".pdf"
End If
Next ws
End Sub

✅ Practical automation bridge between Excel and external tools.

---

✅ Best Practices for Professional VBA Developers

✔ Always qualify your objects (Workbook, Worksheet, Range)
✔ Keep loops clean and descriptive — avoid “magic” variable names
✔ Combine If conditions and Exit For for better control
✔ Turn off ScreenUpdating and Calculation during heavy loops
✔ Never modify the collection while iterating
✔ Comment your loops to improve readability and maintenance

Following these best practices ensures that your For Each macros are efficient, robust, and maintainable in any professional environment.

---

✅ Summary：Mastering the For Each Statement in Excel VBA

• The For Each statement lets you iterate cleanly through collections like Cells, Sheets, and Shapes.
• It’s simpler and safer than traditional index loops.
• Combine with conditions, nested loops, and object properties for flexibility.
• Always optimize performance for large data operations.
• Use it for real-world applications such as reporting, automation, and data processing.

By mastering the For Each statement, you’ll gain complete control over Excel’s object model — creating cleaner, faster, and more intelligent VBA automations that scale effortlessly from small projects to enterprise-level workflows.