Working with For Each Loop - VBA

The VBA For Each Loop will loop through all objects in a collection:

• All cells in a range
• All worksheets in a workbook
• All shapes in a worksheet
• All open workbooks
• You can also use Nested For Each Loops to
• All cells in a range on all worksheets
• All shapes on all worksheets
• All sheets in all open workbooks

The syntax is:

For Each Object in Collection [Do Something] Next [Object]

Where:

1. Object – Variable representing a Range, Worksheet, Workbook, Shape, etc. (ex. rng)
2. Collection – Collection of objects (ex. Range(“a1:a10”)
3. [Do Something] – Code block to run on each object
4. Next [Object] – Closing statement. [Object] is optional, however strongly recommended.

For Each Cell in Range

This code will loop through each cell in a range:

Sub ForEachCell_inRange() Dim cell As Range For Each cell In Range("a1:a10")     cell.Value = cell.Offset(0,1).Value Next cell End Sub

For Each Worksheet in Workbook

This code will loop through all worksheets in a workbook, unprotecting each sheet:

Sub ForEachSheet_inWorkbook() Dim ws As Worksheet For Each ws In Worksheets     ws.Unprotect "password" Next ws End Sub

For Each Open Workbook

This code will save and close all open workbooks:

Sub ForEachWB_inWorkbooks() Dim wb As Workbook For Each wb In Workbooks     wb.Close SaveChanges:=True Next wb End Sub

For Each Shape in Worksheet

This code will delete all shapes in the active sheet.

Sub ForEachShape() Dim shp As Shape For Each shp In ActiveSheet.Shapes     shp.Delete Next shp End Sub

For Each Shape in Each Worksheet in Workbook

You can also nest For Each Loops. Here we will loop through all shapes in all worksheets in the active workbook:

Sub ForEachShape_inAllWorksheets() Dim shp As Shape, ws As Worksheet For Each ws In Worksheets     For Each shp In ws.Shapes         shp.Delete     Next shp Next ws End Sub

For Each – IF Loop

You can use an If statement within a loop, performing actions only if certain criteria is met.

This code will hide all blank rows in a range

Sub ForEachCell_inRange() Dim cell As Range For Each cell In Range("a1:a10")     If cell.Value = "" Then _        cell.EntireRow.Hidden = True Next cell End Sub

Loop Through Rows

This will loop through all the rows in a column:

Public Sub LoopThroughRows() Dim cell As Range For Each cell In Range("A:A")     If cell.value <> "" then MsgBox cell.address &": " &  cell.value Next cell End Sub

Loop Through Columns

This will loop through all columns in a row:

Public Sub LoopThroughColumns()  Dim cell As Range For Each cell In Range("1:1")     If cell.Value <> "" Then MsgBox cell.Address & ": " &  cell.Value Next cell End Sub

Loop Through Files in a Folder

This code will loop through all files in a folder, creating a list:

Sub LoopThroughFiles () Dim oFSO As FileSystemObject Dim oFolder As folder Dim oFile As File Dim i As Integer Set oFSO = new "Scripting.FileSystemObject") Set oFolder = oFSO.GetFolder("C:\Demo) i = 2 For Each oFile In oFolder.Files     Range("A" & i).value = oFile.Name     i = i + 1 Next oFile End Sub

Loop Through Array

This code will loop through the array ‘arrList’:

Sub looparr() Dim Arrlist(10) as Variant For i = LBound(arrList) To UBound(arrList)     MsgBox arrList(i) Next i End Sub

The LBound function gets the “lower bound” of the array and UBound gets the “upper bound”.