Day 6 — The last row, Do loops and For Each

Hard-coding row 17 works until somebody adds row 18. This is the day your macros stop breaking.

The problem with For i = 2 To 17

Next month the file has 40 rows. Your macro fills 16 of them and quietly leaves the rest blank, and nobody notices until the report is out. The fix is to ask the sheet how far the data goes.

The last row idiom

Cells(Rows.Count, 1).End(xlUp).Row

Read it right to left: start at the very bottom of column A, then jump up to the last cell that has something in it, then take its row number. It is the equivalent of clicking A1048576 and pressing Ctrl + Up.

Sub LastRow()
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Debug.Print "Data ends on row", lastRow
    Debug.Print "That is", lastRow - 1, "orders"
End Sub

Now the loop looks after itself:

Sub FillTotalsProperly()
    Dim i As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("H1").Value = "Total"

    For i = 2 To lastRow
        Cells(i, 8).Value = Cells(i, 6).Value * Cells(i, 7).Value
    Next i

    Debug.Print "Filled rows 2 to", lastRow
End Sub

That macro works on 16 rows and it works on 60,000. This is the single most useful line in everyday VBA - learn it by heart.

Pick a column that is never empty Counting up column A only works if column A is filled on every row. If your ID column has gaps, use a column that does not - or you will silently process half the data.

Do While - loop until a condition stops being true

A For loop needs to know how many times up front. A Do loop does not, which suits "keep going until you hit a blank".

Sub WalkDown()
    Dim r As Long

    r = 2

    Do While Cells(r, 1).Value <> ""
        Debug.Print "Row " & r & ": " & Cells(r, 4).Value
        r = r + 1
    Loop

    Debug.Print "Stopped at row", r
End Sub

Forget r = r + 1 and it never ends The condition never changes, so the loop runs forever. In Excel you press Ctrl + Break to escape. Here the playground gives up after 300,000 steps and tells you. Try deleting that line and running it.

Do Until, and testing at the bottom

Do Until is the same idea worded the other way. Putting the test on the Loop line instead guarantees the body runs at least once.

Sub TwoFlavours()
    Dim r As Long

    r = 2
    Do Until Cells(r, 1).Value = ""
        r = r + 1
    Loop
    Debug.Print "Do Until stopped at", r

    r = 2
    Do
        r = r + 1
    Loop While Cells(r, 1).Value <> ""
    Debug.Print "Loop While stopped at", r
End Sub

For Each - walk a range without counting

When you want every cell in a block and do not care about row numbers, For Each says so directly.

Sub CountNorth()
    Dim c As Range
    Dim n As Long

    For Each c In Range("C2:C17")
        If c.Value = "North" Then
            n = n + 1
        End If
    Next c

    Debug.Print "North orders:", n
End Sub

Inside the loop c is a proper cell, so it has everything a cell has - including Offset, which lets you reach the rest of the row:

Sub NorthValue()
    Dim c As Range
    Dim total As Double

    For Each c In Range("C2:C17")
        If c.Value = "North" Then
            total = total + c.Offset(0, 3).Value * c.Offset(0, 4).Value
        End If
    Next c

    Debug.Print "North value:", total
End Sub

Which loop, when For i = 2 To lastRow - you need the row number, or you are writing to another column. This is most report code.
For Each - you want every cell in a block and the row number is irrelevant. Reads beautifully, and it is faster.
Do While - you do not know where the end is and cannot ask. Rarer than you would think.

Try these yourself

Find the last used row of column D and print it.
Rewrite yesterday NorthReport so it uses lastRow instead of 17.
Use Do While to walk down column A and print each order ID until a blank stops it.
Use For Each on E2:E17 to count the Monitor orders.
Explain what Cells(Rows.Count, 1).End(xlUp).Row does, one step at a time.

← PreviousDay 5 — For loops - doing it to every row Next →Day 7 — Text functions - cleaning messy data