What Most People Miss About Do Until Excel VBA

A 2024 workplace survey found 83% of Excel users avoid Do Until loops — yet they solve repetitive data cleanup tasks in under 10 lines. Not because the syntax is hard. Because most people misplace the exit condition, or don’t realize it’s the only loop that tests after running the block — a tiny detail that changes everything.

The Problem

You’ve got raw sales entries dumped from a CRM into Sheet1 — inconsistent spacing, blank rows between records, and headers repeating every 7–9 rows. Manual deletion takes 12 minutes per file. Worse: when you try to automate it with For Each, you hit runtime error '1004' — because deleting rows shifts cell references mid-loop. Your macro crashes at row 47, leaving half the junk behind.

Row	A (Name)	B (Amount)	C (Date)
1	Sales Report
2	Sarah Chen	$45,200	2024-03-15
3
4	Acme Corp	$18,900	2024-03-16
5	Sales Report
6	Jin Park	$62,150	2024-03-17
7
8	Nexus Labs	$33,400	2024-03-18
9	Sales Report
10	Lena Dubois	$27,800	2024-03-19

Symptom → Cause → Fix

Symptom: Macro stops at row 5, skipping all rows below.
Cause: Using For i = 1 To 100 while deleting rows — index i jumps over the next physical row after deletion.
Fix: Loop backward (For i = 100 To 1 Step -1) OR use Do Until with dynamic range checking.
Symptom: “Subscript out of range” on Cells(i, 1).Value.
Cause: Assuming row count stays fixed — but blank rows mean last used row isn’t reliable.
Fix: Test for empty cell *inside* the loop, not before it starts.
Symptom: Infinite loop freezing Excel.
Cause: Forgetting to increment the counter inside Do Until.
Fix: Always place i = i + 1 *before* the Loop Until line — or better yet, use Do While Not IsEmpty(...).

The Solution

Here’s the clean version — 9 lines, zero crashes, handles variable row counts:

Sub CleanSalesData()
    Dim i As Long
    i = 1
    Do Until IsEmpty(Cells(i, 1))
        If Cells(i, 1).Value = "Sales Report" Or _
           Cells(i, 1).Value = "" Then
            Rows(i).Delete
        Else
            i = i + 1
        End If
    Loop
End Sub

Note: The key is not incrementing i when deleting — because the next row slides up into position i. So we only advance i when we keep the row. This is why Do Until shines here — it lets you control flow based on state *after* each action.

Run it on the messy table above. Result:

Row	A (Name)	B (Amount)	C (Date)
1	Sarah Chen	$45,200	2024-03-15
2	Acme Corp	$18,900	2024-03-16
3	Jin Park	$62,150	2024-03-17
4	Nexus Labs	$33,400	2024-03-18
5	Lena Dubois	$27,800	2024-03-19

That’s it. No counting rows first. No offset math. Just test, act, repeat.

Going Further

You can nest conditions cleanly:

Do Until IsEmpty(Cells(i, 1))
    Select Case True
        Case Cells(i, 1).Value Like "*Report*"
            Rows(i).Delete
        Case Cells(i, 2).Value < 1000
            Cells(i, 3).Interior.Color = RGB(255, 230, 230)
        Case InStr(Cells(i, 1).Value, ",") > 0
            Cells(i, 1).Value = Trim(Split(Cells(i, 1).Value, ",")(0))
        Case Else
            i = i + 1
    End Select
Loop

Or combine with worksheet objects for safety:

With Worksheets("Sheet1")
    i = 1
    Do Until IsEmpty(.Cells(i, 1))
        If .Cells(i, 1).Value = "Sales Report" Then
            .Rows(i).Delete
        Else
            i = i + 1
        End If
    Loop
End With

Surprising tip: Do Until False creates an infinite loop — useful for polling status cells. But always include an Exit Do with a timeout:

Dim startTime As Double
startTime = Timer
Do Until False
    If .Cells(1, 10).Value = "READY" Then Exit Do
    If Timer - startTime > 30 Then Exit Do ' timeout after 30 sec
    DoEvents ' prevents freeze
Loop

When NOT to Use This

Don’t reach for Do Until if:

Your dataset fits in memory and has predictable structure — use AutoFilter instead. It’s faster and safer.
You’re looping over ranges with formulas referencing other sheets — Do Until won’t auto-refresh dependencies mid-loop.
You need precise row indexing for logging (e.g., “error at row 42”) — Do Until shifts rows as you delete, so original positions get lost.
You’re processing >10,000 rows without disabling screen updating. Add Application.ScreenUpdating = False before the loop — and = True after.

Also: never use Do Until Cells(i, 1) = "" on a column containing formulas that return "" — it sees the formula, not the displayed value. Use IsEmpty() or Len(Trim()) = 0 instead.

Keyboard Shortcuts

Action	Shortcut	Notes
Open VBA Editor	`Alt + F11`	Always your first move
Insert New Module	`Alt + I + M`	In VBA Editor — no mouse needed
Run Current Macro	`F5`	Or `F8` to step through line-by-line
Toggle Breakpoint	`F9`	Click left gutter or press F9 on any line
Immediate Window	`Ctrl + G`	Type `? Cells(5,1).Value` to test values live