For Each Loop in Excel VBA

A For Each loop is used to execute a statement or a group of statements for each element in an array or collection

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

Syntax of for each loop

For Each Object in Collection

           Statement 1…

Statement 2…

Next Object

1) Example To Illustrate for Each Loop for every cell in a range
‘ This procedure will loop through each cell in range A1:A10, setting the cell to its right equal to itself.
Sub ForEachCell()
Dim Cell As Range
For Each Cell In Sheets(“Sheet1”).Range(“A1:A10”)
Cell.Offset(0, 1).Value = Cell.Value
Next Cell
End Sub

For each loop with if and else examples
Sub If_Loop()
Dim Cell As Range
For Each Cell In Range(“A2:A6”)
If Cell.Value > 0 Then
Cell.Offset(0, 1).Value = “Positive”
ElseIf Cell.Value < 0 Then
Cell.Offset(0, 1).Value = “Negative”
Else
Cell.Offset(0, 1).Value = “Zero”
End If
Next Cell
End Sub

2) Example To Illustrate for Each Loop for every worksheet in a workbook

Sub testworksheet()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Range(“A1”).Value = “myitschool”
Next Ws
End Sub

3) Example To Illustrate for Each Loop for every shapes in a worksheet

‘This procedure will loop through each shape in Sheet1, deleting each one
Sub ForEachShape()
Dim Shp As Shape
For Each Shp In Sheets(“Sheet1”).Shapes
Shp.Delete
Next Shp
End Sub

‘This procedure will delete all shapes in a workbook
Sub DeleteAllShapesOnAllWorksheets()
Dim Sheet As Worksheet
Dim Shp As Shape
For Each ws In Sheets
For Each Shp In ws.Shapes
Shp.Delete
Next Shp
Next ws
End Sub

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

Sub ForEachCharts()
Dim cht As ChartObject
For Each cht In Sheets(“Sheet1”).ChartObjects
cht.Delete
Next cht
End Sub

‘This procedure will loop through each PivotTable in Sheet1, clearing each one
Sub ForEachPivotTables()
Dim pvt As PivotTable
For Each pvt In Sheets(“Sheet1”).PivotTables
pvt.ClearTable
Next pvt
End Sub

‘This procedure will loop through each Table in Sheet1, deleting each one.
Sub ForEachTables()
Dim tbl As ListObject
For Each tbl In Sheets(“Sheet1”).ListObjects
tbl.Delete
Next tbl
End Sub

‘This procedure will loop through each Sheet in workbook type myitschool in range A1.
Sub testworksheet()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range(“A1”).Value = “myitschool”
Next ws
End Sub

‘Hide All the Sheets
Sub testworksheet2()
Dim ws As Worksheet
On Error Resume Next ‘ if error come it will automatically resume to next loop
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVeryHidden
Next ws
End Sub

Sub HideAllSheets()
Dim ws As Worksheet
For Each ws In Sheets
ws.Visible = xlSheetHidden
Next ws
End Sub

‘Hide All the Sheets except particular name
Sub testworksheet4()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> “myitschool” Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
End Sub

‘Unhide All the Sheets
Sub testworksheet3()
On Error Resume Next ‘ if error come it will automatically resume to next loop
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

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

‘Unhide All the Sheets
Sub ForEachSheets()
Dim ws As Worksheet
For Each ws In Sheets
ws.Visible = True
Next Sheet
End Sub

‘Protect All the Sheets
Sub testworksheet5()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=”myitschool”
Next ws
End Sub

‘UnProtect All the Sheets
Sub testworksheet6()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:=”myitschool”
Next ws
End Sub

4) Example To Illustrate for Each Loop for every open workbook
‘This procedure will loop through each workbook, closing each one
Sub ForEachWorkbooks()
Dim wb As Workbook
For Each wb In Workbooks
wb.Close
Next wb
End Sub

‘This procedure will close all open workbooks after saving changes
Sub CloseAllWorkbooks()
Dim wb As Workbook
For Each wb In Workbooks
wb.Close SaveChanges:=True
Next wb
End Sub

5) Example To Illustrate for Each Loop in an Array

Sub testarray ()
‘Months are an array
Months = Array(“January”, “February”, “march”, “April”, “may”, “June”, “July”, “august”, “September”, “October”, “November”, “December”)
Dim Monthnames as Variant
‘Iterating using for each loop
For Each Item in Months
Monthnames = Monthnames & Item & Chr(10)
Next
MsgBox Monthnames
End Sub