Stop Using For Loops for Dynamic Ranges — Try Do While Instead

The first thing most people do when they need to process rows until a blank cell appears is write a For i = 1 To 1000 loop. That’s dangerous — especially if your data grows past row 1000 or shrinks to 12 rows. You’ll either crash Excel or skip entries. (Trust me, I learned this the hard way after a client’s report broke on their fiscal year-end close.)

The Setup

You’re maintaining a sales log in Sheet1, starting at cell A2. Column A holds agent names, B has order IDs, C has amounts, and D has dates. The list isn’t fixed — some days it’s 7 rows, others 42. No header row is guaranteed; the first non-blank cell in column A is always the start.

A (Agent)	B (Order ID)	C (Amount)	D (Date)
Sarah Chen	ORD-7821	$12,450	2024-03-15
James Rivera	ORD-7822	$8,920	2024-03-16
Maya Patel	ORD-7823	$15,600	2024-03-16
David Kim	ORD-7824	$3,200	2024-03-17
Lena Torres	ORD-7825	$22,100	2024-03-17
Rajiv Mehta	ORD-7826	$9,750	2024-03-18
Sophie Wu	ORD-7827	$14,800	2024-03-18
Andre Dubois	ORD-7828	$6,300	2024-03-19
Nina Okoye	ORD-7829	$11,200	2024-03-19

The Challenge

You need to flag every row where the amount in column C exceeds $10,000 — by adding "HIGH" in column E. But you can’t assume the last row is row 10. Some weeks there are 2 rows. Others, 87. A For Each loop over Range("A:A") would crawl through 1 million cells. A For i = 1 To 1000 loop risks missing data or hitting empty sheets with an error.

Worse: if someone inserts a blank row mid-list (say, row 5), a rigid loop stops early. You need something that reads *until it hits the first truly blank row* — not just any blank cell.

Walking Through It

Open the VBA editor with Alt + F11. Insert a new module (Insert → Module). Paste this:

Sub FlagHighValueOrders()
    Dim i As Long
    i = 2 ' Start at row 2 — A2 is first data row
    
    Do While Not IsEmpty(Cells(i, "A"))
        If Cells(i, "C").Value > 10000 Then
            Cells(i, "E").Value = "HIGH"
        End If
        i = i + 1
    Loop
End Sub

Let’s walk through what happens:

• Line 3 sets i = 2 — because your data starts at A2.
• Line 5 checks Cells(i, "A") — not column C or D. Why? Because column A is your anchor: agent names are required, so its first blank signals end-of-data.
• If A2 isn’t empty, we check C2. If >10000 → write “HIGH” in E2.
• Then i = i + 1 moves us to row 3 — and the loop repeats.

Here’s the state after each full pass through rows 2–4:

Row	A (Agent)	C (Amount)	E (Flag)
2	Sarah Chen	$12,450	HIGH
3	James Rivera	$8,920
4	Maya Patel	$15,600	HIGH

Notice: row 3 stays blank in column E — correct behavior. And the loop keeps going until it hits row 11, where Cells(11, "A") returns Empty.

Surprising tip: Never use Do While Cells(i, "A") <> "". It fails if A5 contains a formula returning "" — Excel sees that as *not empty*, even though it looks blank. IsEmpty() checks for true emptiness — no value, no formula.

The Result

After running the macro, column E is populated only where needed — clean, safe, and adaptive. Here’s the final output for all 10 rows:

A (Agent)	B (Order ID)	C (Amount)	D (Date)	E (Flag)
Sarah Chen	ORD-7821	$12,450	2024-03-15	HIGH
James Rivera	ORD-7822	$8,920	2024-03-16
Maya Patel	ORD-7823	$15,600	2024-03-16	HIGH
David Kim	ORD-7824	$3,200	2024-03-17
Lena Torres	ORD-7825	$22,100	2024-03-17	HIGH
Rajiv Mehta	ORD-7826	$9,750	2024-03-18
Sophie Wu	ORD-7827	$14,800	2024-03-18	HIGH
Andre Dubois	ORD-7828	$6,300	2024-03-19
Nina Okoye	ORD-7829	$11,200	2024-03-19	HIGH

What Could Go Wrong

Three real issues I’ve seen in production macros — all tied to Do While misuse:

• Infinite loop due to forgotten increment: If you omit i = i + 1 inside the loop, Excel freezes. The condition never changes. Press Ctrl + Break to halt — then check your counter line.
• Starting too high or too low: If your data actually starts at A3 (not A2), and you set i = 2, you’ll process a blank row — possibly writing “HIGH” in E2 for no reason. Always verify the first data row manually before coding.
• Using the wrong column for the exit check: Checking Cells(i, "C") instead of Cells(i, "A") breaks if an order amount is zero or blank — even if agents keep coming. Anchor to the column that *must* be filled.

Here’s a quick-reference cheat sheet for next time you reach for Do While:

Scenario	Use This Pattern	Why
Data starts at A5, ends at first blank in column A	i = 5 Do While Not IsEmpty(Cells(i, "A"))   ...   i = i + 1 Loop	Starts where data lives — avoids blank rows above.
You need to skip header row in row 1	i = 2 Do While i <= Cells(Rows.Count, "A").End(xlUp).Row   ...   i = i + 1 Loop	Finds last non-blank in column A — safer than IsEmpty() if formulas exist.
Process until column D says "Complete"	i = 2 Do While Cells(i, "D").Value <> "Complete"   ...   i = i + 1 Loop	Exit condition based on content — not emptiness.
You want to stop *before* first blank (not at it)	i = 2 Do   If IsEmpty(Cells(i, "A")) Then Exit Do   ...   i = i + 1 Loop	This is a Do...Loop — checks condition at the end. Useful for “process first, then decide.”