For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

VBA loops and conditions: For Each, Do While, and If/Select Case

VBA.C5 For i = 2 To lastRow … Next i
VBA loops and conditions: For Each, Do While, and If/Select Case — Excel example preview

Looping across worksheet rows (and bottom-up with Step -1 when deleting rows) to flag overdue records automatically.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Advanced

The Ultimate Excel Advanced Filters In VBA [Full Training Course]

Excel For Freelancers

Open on YouTube (opens in a new tab)
Excel For Freelancers

Loading preview…

Almost every useful macro is some combination of “do this for each of these” and “but only if this is true.” VBA gives you a few loop styles and two ways to branch — knowing which to reach for makes code much easier to read.

For…Next

Use this when you know how many times to repeat something, usually counting up (or down) through numbers:

Dim i As Long
For i = 1 To 10
    Cells(i, 1).Value = i * 2
Next i

For Each…Next

Use this when you want to act on every item in a collection — every cell in a range, every worksheet in a workbook, every open workbook — without tracking an index yourself:

Dim c As Range
For Each c In Range("B2:B50")
    ' c refers to one cell at a time
Next c

Do While / Do Until

Use these when you don’t know in advance how many times you’ll loop — you just want to keep going while (or until) some condition holds:

Dim i As Long
i = 1

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

MsgBox "First blank row is " & i

Do Until Cells(i, 1).Value = "" would do the same thing phrased the other way around — pick whichever reads more naturally for the check you’re making.

If…Then…Else

For a small number of conditions:

If c.Value > 1000 Then
    c.Font.Bold = True
Else
    c.Font.Bold = False
End If

Select Case

For one variable being checked against several possible ranges or values, Select Case reads more cleanly than a chain of ElseIf:

Dim score As Integer
score = Range("C2").Value

Select Case score
    Case Is >= 90
        Range("D2").Value = "A"
    Case 80 To 89
        Range("D2").Value = "B"
    Case 70 To 79
        Range("D2").Value = "C"
    Case Else
        Range("D2").Value = "F"
End Select

Worked example: bold cells over a threshold

Combining a For Each loop with an If test — a genuinely common task, flagging values worth a second look:

Sub BoldHighValues()
    Dim c As Range
    Dim threshold As Double
    threshold = 1000

    For Each c In Range("B2:B50")
        If c.Value > threshold Then
            c.Font.Bold = True
        End If
    Next c
End Sub

Worked example: rename every worksheet

For Each also works over worksheets, not just cells:

Sub RenameSheetsWithPrefix()
    Dim ws As Worksheet
    Dim i As Integer
    i = 1

    For Each ws In ThisWorkbook.Worksheets
        ws.Name = "Region" & i
        i = i + 1
    Next ws
End Sub

One gotcha worth knowing before you run something like this: sheet names are capped at 31 characters, can’t contain characters like \ / ? * [ ], and must be unique. If your naming logic would ever produce the same name twice — or a name that already exists elsewhere in the workbook — this will raise a runtime error partway through, leaving some sheets renamed and others not. Test the naming logic on a copy of the workbook first.

Leaving a loop early

Two keywords, often confused:

  • Exit For / Exit Do — stop looping, carry on with the rest of the procedure.
  • Exit Sub — abandon the procedure entirely.

Stopping at the first match is both faster and clearer than letting the loop run to the end with a flag variable:

Dim c As Range
Dim foundRow As Long

For Each c In Range("A2:A10000")
    If c.Value = "INV-1042" Then
        foundRow = c.Row
        Exit For
    End If
Next c

If foundRow > 0 Then MsgBox "Row " & foundRow Else MsgBox "Not found"

There is no Continue in VBA — no way to skip to the next iteration. Invert the condition instead: wrap the body in If Not <skip condition> Then ... End If.

Nested loops: name the counters

Nested loops over rows and columns are routine. Use descriptive counter names rather than i and j — mixing them up produces code that runs happily and gives wrong answers, which is far worse than code that crashes.

Dim r As Long, col As Long

For r = 2 To 100
    For col = 2 To 13
        If Cells(r, col).Value < 0 Then
            Cells(r, col).Interior.Color = RGB(255, 199, 206)
        End If
    Next col
Next r

Next col before Next r — the inner loop always closes first. Getting this backwards is a compile error, which is the one mercy here.

Three mistakes that cause most loop bugs

1. Deleting rows forwards. When you delete row 5, everything shifts up — the old row 6 becomes row 5, and your loop moves to row 6 and skips it. With consecutive blanks you delete roughly half of them and the result looks almost right, which is how it survives testing.

Always iterate backwards when deleting:

For i = lastRow To 2 Step -1
    If Cells(i, 1).Value = "" Then Rows(i).Delete
Next i

Faster still is collecting the rows with Union and deleting once — see VBA snippets you’ll reuse constantly.

2. Touching the worksheet inside the loop. Every Cells(i, 1).Value is a cross-boundary call into Excel. At 50,000 rows the cost dominates everything else, and the usual complaint — “my macro takes four minutes” — is almost always this.

Read once into an array, loop the array, write once:

Dim data As Variant
data = Range("A2:C50000").Value

For i = 1 To UBound(data, 1)
    If data(i, 2) > 1000 Then data(i, 3) = "High"
Next i

Range("A2:C50000").Value = data

Same logic, typically 50–100× faster. The one thing it cannot do is set formatting — Font.Bold and Interior.Color need the real cells — so for formatting work, build a Union range in the loop and apply the format once at the end.

3. A Do While whose condition never becomes false. Forget the i = i + 1 and Excel hangs with no error and no way back except Ctrl + Break (which does not always land) or Task Manager.

While developing a Do loop, add a guard:

Dim guard As Long

Do While Cells(i, 1).Value <> ""
    i = i + 1
    guard = guard + 1
    If guard > 100000 Then
        MsgBox "Loop guard tripped - check the exit condition"
        Exit Do
    End If
Loop

Remember that a macro cannot be undone, so an infinite loop that was halfway through writing cells leaves the workbook in a state Ctrl + Z will not fix.

If versus Select Case, in practice

Use If for one or two conditions, or when the conditions test different things:

If c.Value > 1000 And c.Offset(0, 1).Value = "Open" Then

Select Case cannot express that — it tests one expression against several possible values. When that is what you are doing, it is clearly better:

Select Case region
    Case "North", "North East"
        rate = 0.1
    Case "South"
        rate = 0.12
    Case Else
        rate = 0.08
End Select

Note Case "North", "North East" — several values on one line, which an ElseIf chain has to spell out with Or. And always write a Case Else, even if it only raises a message. Unexpected values are the normal reason a calculation comes out wrong rather than erroring, and the silent fall-through is what hides it.

Stepping through a loop

The fastest way to understand a loop that is not doing what you expect: put the cursor in the procedure and press F8 to step one line at a time, watching the worksheet update behind the VBE window. Hover over a variable to see its value, or add it to the Watch window.

For a long loop, set a breakpoint (F9) on the line inside the If so execution stops only when the interesting branch is taken. VBA editor basics covers the debugging tools, and error handling covers what to do when a loop hits a row it cannot process — generally skip and log it rather than stopping, so one bad row does not abandon the other 9,999.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Turn the idea into a repeatable Excel workflow you can explain, rebuild, and review later. For this article, the goal is to practice: Which loop to reach for and why, how If and Select Case differ in practice, and the three mistakes — deleting forward, looping cells one at a time, and the infinite Do While — that cause most loop bugs.

Practice workbook setup

Create a small practice workbook with one raw-data sheet, one working sheet, and one final output sheet.

Practice workflow

  1. Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
  2. Write a short note beside the result explaining what each step is doing and why it matters.
  3. Change one input value and confirm the output updates in the way you expected.
  4. Save a clean copy of the workbook before experimenting further.

Quality checks

  • Inputs, calculations, and final outputs are separated clearly.
  • Headings describe the data without relying on memory or hidden context.
  • The final result can be understood by someone who did not build the workbook.

Common mistakes

  • Mixing raw data and manual adjustments in the same cells.
  • Skipping a quick review after the result looks correct.
  • Building a one-off fix instead of a repeatable workflow.

Next actions

  • Apply the same pattern to a real workbook with 20 to 50 rows of sample data.
  • Add one note that explains when you would not use this approach.

Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.