To loop through rows in VBA without Excel freezing, read the whole range into an array first, do the work in memory, then write the results back in one go. Reading and writing the sheet cell by cell is what makes a loop slow: each individual cell access crosses between VBA and Excel, and on ten thousand rows that crossing happens ten thousand times. Loading ws.Range("A1:C10000").Value into a variable does it once.
For a few hundred rows a plain loop is fine and easier to read. The array approach earns its keep from roughly a thousand rows upward.
Finding the last row first
Never loop to a hardcoded row number. This finds the last used row in column 1, the same way pressing Ctrl+Up from the bottom of the sheet would:
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Declare row counters as Long, not Integer. Integer overflows above 32,767 and a modern sheet has over a million rows.
The straightforward loop
Sub FlagOverdue()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, 3).Value < Date Then
ws.Cells(i, 4).Value = "Overdue"
End If
Next i
End Sub
Starts at row 2 to skip the header. Perfectly good up to a few thousand rows.
The array version for large data
Same job, but the sheet is touched exactly twice:
Sub FlagOverdueFast()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim data As Variant
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
data = ws.Range("A2:D" & lastRow).Value
For i = 1 To UBound(data, 1)
If data(i, 3) < Date Then
data(i, 4) = "Overdue"
End If
Next i
ws.Range("A2:D" & lastRow).Value = data
End Sub
One read, one write. The array is 1-based and two-dimensional, and its indexes are positions within the range, not sheet row numbers.
The index shift catches everyone once
Because the range starts at A2, data(1, 1) is cell A2, not A1. If you need the real sheet row inside the loop it is i + 1. Getting this wrong writes every value one row out, and the code runs without error.
Deleting rows: always loop backwards
Deleting a row shifts everything below it up, so a forward loop skips the row immediately after each deletion. Count down instead:
For i = lastRow To 2 Step -1
If ws.Cells(i, 1).Value = "" Then
ws.Rows(i).Delete
End If
Next i
Working upward means a deletion never moves a row you have not looked at yet.
Common questions
Why does Excel show “Not Responding” during my loop?
Excel is busy and not processing screen messages, which Windows reports as not responding even though the macro is running normally. Switching off screen updating and calculation usually shortens it enough to disappear. DoEvents inside the loop keeps the window responsive but makes the loop slower, so use it sparingly.
Can I show progress while it runs?
Yes, with Application.StatusBar = "Processing row " & i. Update it every few hundred rows rather than every row, and set it back to False at the end or your message stays there.
Why is my array 1-based when VBA arrays usually start at 0?
An array read from a range always comes back 1-based and two-dimensional, even for a single column, because it mirrors the range’s own row and column positions. That is why UBound(data, 1) gives the row count.
Can I avoid the loop entirely?
Often, yes. AutoFilter plus a bulk operation, SpecialCells, or writing a formula to the whole column at once will beat any loop. The fastest loop is the one you did not need to write.
When Formulas Aren’t Enough
Loops are where small automations become real tools:
- Validation across every row — checking each record against rules before anything is committed
- Building documents in bulk — one PDF or email per row, generated in a single run
- Reconciling two datasets — matching thousands of records and reporting what does not line up
A custom VBA tool does this on your real volume, with a progress indicator and a log of what it changed.
Processing Thousands of Rows by Hand?
Tell us what the rules are and we’ll build the tool that applies them.
Start a Project