Course contents · Macros and VBA

Loops: For, For Each, Do

Open the practice in the trainer →

The power of macros is repeating actions for hundreds of rows.

For i = 2 To 11 … Next ia counter from 2 to 11; Step 2 — the step, Step -1 — backwards (needed when deleting rows)
For Each c In Range("B2:B11") … Next cgoes through the cells of a range: c is the current cell
Do While condition … Looprepeat while the condition is true; Do Until — while it is false
Exit For, Exit Doleave the loop early

How many rows are in the table?

The classic trick: start at the very bottom cell of the column and "jump" up, like Ctrl+↑:

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
    Cells(i, 3).Value = Cells(i, 2).Value * 1.2
Next i

This way the macro adapts to the length of the table, not just today's 10 rows.

i = 2
Do While Cells(i, 1).Value <> ""
    total = total + Cells(i, 2).Value
    i = i + 1
Loop

If a loop never ends, Excel "freezes"; stop the macro with Esc or Ctrl+Break. The trainer stops a macro that runs too long by itself.

Tasks in the VBA editor

  1. With a For loop: C2:C11 get the amount with 20% VAT (B × 1.2) for every row.
  2. With a For Each loop, fill amounts over 1000 in B2:B11 with RGB(255, 199, 206).
  3. Find the number of the last filled row with Cells(Rows.Count, 1).End(xlUp).Row and write it into G1.
  4. With a Do While loop: while column A is not empty, add up the amounts in B; the total goes into G2.
  5. Number the rows: D2:D11 get 1 to 10 — with a loop.

Solve the tasks in the trainer →

Check yourself

  1. Why does a loop that deletes rows go from the bottom up (Step -1)?
  2. What does Cells(Rows.Count, 1).End(xlUp).Row return?