Excel like a finance pro.
VBA · Control flow
For Each ... Next
Loops over every item in a collection: cells in a range, sheets in a workbook.
When to use it
For Each ... Next visits every item in a collection: cells in a range, sheets in a workbook, files in a folder. No counter needed.
The code
- Code
For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws
Worked examples
Every sheet
For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws → Unhides every sheet
Sheets.
Every cell
For Each c In Range("A2:A100") If c.Value < 0 Then c.Font.Color = vbRed Next c → Reds every negative
Cells.
Dictionary keys
For Each key In dict.Keys Debug.Print key, dict(key) Next key → Every entry in a Dictionary
Dictionaries.
Worth knowing
- Declare the loop variable as the item type (Worksheet, Range) for autocomplete.
- For Each over cells is slower than an array for large ranges.
- Do not add or remove items from the collection while looping it.
Where it goes wrong
- Deleting sheets or rows inside a For Each skips items.
- The loop variable for a range must be Range or Variant.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.