Wauvel

Excel like a finance pro.

← The library

VBA · Control flow

For Each ... Next

Loops over every item in a collection: cells in a range, sheets in a workbook.

Daily driverDifficulty 1050 · Capable
Practice · 2 questions →

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.