VBA Delete Rows in Excel — Delete Rows, Blank Rows and Rows by Condition (Loop Backwards!)

TL;DR — Deleting a row is a structural edit: it doesn't blank the row, it removes it and slides every row below up by one. That single fact is behind the most common VBA bug there is — a forward loop that deletes rows skips every row that followed a deleted one, because the row numbers shift under the loop. The fix is one word: loop backwards (For i = last To first Step -1). Delete the whole row with .EntireRow.Delete, and for many scattered rows, collect them into one Union and delete once — it's faster and it sidesteps the shifting problem entirely.

' Delete every row whose column A is empty — loop BOTTOM to TOP.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
Dim lastRow As Long, i As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = lastRow To 2 Step -1                 ' <-- backwards, or you skip rows
    If Trim(ws.Cells(i, "A").Value) = "" Then ws.Rows(i).EntireRow.Delete
Next i

"Delete these rows" sounds like the simplest task in Excel automation, and it is the one that trips up almost everyone the first time. The reason isn't the Delete method — it's that deleting changes the very thing you're looping over. Get the direction of travel right and delete-by-condition is trivial; get it wrong and your macro silently leaves half the matching rows behind. This guide is built around that one idea, then the practical patterns that follow from it.

What you'll learn

The mental model — deleting removes a row and slides everything below up
The rule that matters most — loop backwards, or a forward loop skips rows
Why .EntireRow.Delete is the safe target, and what .Delete on a cell does instead
The faster pattern for scattered rows — build a Union, delete once
Stripping blank rows and deleting by condition without breaking reference formulas

The mental model: a delete moves the grid, it doesn't blank a row

There are two completely different operations people both call "delete." Pressing Delete clears a cell — the row stays, the value goes. Right-click ▸ Delete, or Rows(i).Delete in code, removes the row — the row ceases to exist and every row beneath it slides up one to close the gap. The row that was 11 is now 10.

That closing-the-gap behaviour is the whole story. When you delete row 10, row 11's data is now sitting in row 10. Any code that assumed "row 11 still holds what it held a moment ago" is now wrong. This is why deleting rows feels different from every other loop you write: the address of the data you haven't processed yet changes the instant you delete. You're editing the coordinate system while you walk across it.

Keep that picture in mind — below the deleted row, everything shifts up — and every rule below is just a consequence of it.

The rule that matters most: loop backwards or you skip rows

Here is the bug, written the way almost everyone writes it the first time:

' WRONG — a forward loop over rows you're deleting.
For i = 2 To lastRow
    If ws.Cells(i, "A").Value = "DELETE" Then ws.Rows(i).EntireRow.Delete
Next i

Say rows 5 and 6 both say "DELETE". The loop reaches i = 5, deletes row 5, and row 6 slides up to become the new row 5. But the loop now moves on to i = 6 — which is the row that used to be row 7. The old row 6 was never checked. Two adjacent matches, and the second one survives. With a block of matches, you leave behind roughly every other one, and the symptom ("some rows didn't delete") looks random.

The fix costs nothing. Walk from the bottom up:

' RIGHT — delete from the last row back to the first.
For i = lastRow To 2 Step -1
    If ws.Cells(i, "A").Value = "DELETE" Then ws.Rows(i).EntireRow.Delete
Next i

Going backwards, deleting row 6 shifts rows below 6 up — but you've already handled those. The rows you haven't looked at yet are all above the one you just deleted, and their numbers didn't move. The shifting still happens; it just happens behind you where it can't do any harm. Whenever a loop deletes rows, it goes Step -1. Memorise that and half of all delete bugs disappear.

The rule that keeps data aligned: delete the row, not a cell

Delete is available on any range, and the smaller the range, the more dangerous it is. If you write ws.Cells(i, "A").Delete, you delete one cell, and Excel slides the cells below it in column A only upward — leaving columns B, C, D exactly where they were. Every row from that point down is now misaligned: column A shows one record while B and C show the one below it. Nothing errors; your data is just quietly scrambled.

Deleting the whole row keeps every column in step:

ws.Rows(i).EntireRow.Delete        ' the entire row 11 goes; 12, 13... slide up together
ws.Cells(i, "A").EntireRow.Delete  ' identical — .EntireRow promotes a cell to its full row

.EntireRow is the promotion from "this cell" to "the whole row this cell sits in," and it's what you almost always want. Reserve the bare Range.Delete with Shift:=xlUp/xlToLeft for the rare case where you truly mean to move a block of cells, not remove a record.

The faster, safer pattern: collect a Union and delete once

Looping and deleting row-by-row works once you loop backwards, but on large sheets it's slow — each .Delete forces Excel to shift the grid and recalculate. There's a pattern that is both faster and immune to the shifting trap, because it does all the deleting in a single step: build up a Range of every row to remove with Union, then delete the lot at the end.

Dim victims As Range, i As Long
For i = 2 To lastRow                          ' direction doesn't matter — we delete later
    If ws.Cells(i, "A").Value = "DELETE" Then
        If victims Is Nothing Then
            Set victims = ws.Rows(i)
        Else
            Set victims = Union(victims, ws.Rows(i))
        End If
    End If
Next i

If Not victims Is Nothing Then victims.EntireRow.Delete   ' one delete, all rows at once

Because no row is removed until the loop is over, the row numbers never move during the scan — so a forward loop is perfectly safe here. And a single .Delete of a multi-area range is dramatically faster than hundreds of individual deletes. My rule of thumb: a handful of rows, loop backwards; hundreds or thousands, collect a Union (or filter — see below) and delete once. Wrap either in Application.ScreenUpdating = False / Calculation = xlManual and large deletes go from seconds to instant.

Blank rows, conditions, and the reference trap

Delete blank rows has a one-liner that beats any loop — let Excel find the blanks:

On Error Resume Next   ' SpecialCells raises 1004 if there are no blanks
ws.Range("A2:A" & lastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

SpecialCells(xlCellTypeBlanks) returns every empty cell in column A as one multi-area range, and .EntireRow.Delete removes all their rows in a single shot. Two caveats: it keys off one column (a row with A empty but B filled still goes), and it errors when there are no blanks — hence the On Error guard.

Delete by condition on big data, the professional move is AutoFilter: filter to the rows you don't want, delete the visible ones, remove the filter. It's the fastest approach of all because Excel does the matching:

With ws.Range("A1").CurrentRegion
    .AutoFilter Field:=1, Criteria1:="DELETE"
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete  ' skip the header row
End With
ws.AutoFilterMode = False

One trap outlives all of these: deleting rows shifts references. If a formula elsewhere points at =A20 and you delete row 5, Excel rewrites it to =A19 — usually what you want. But if you delete the row a formula depends on, that formula turns into #REF!. Deleting rows out from under live formulas is the classic cause of a sheet full of #REF! errors, so before a bulk delete, know whether anything downstream references the rows you're removing. Finding where your data ends is a related skill — see VBA Last Row.

How ExcelMaster helps

Delete-rows code has a handful of quiet ways to go wrong: the forward loop that skips rows, the single-cell delete that misaligns columns, the SpecialCells call that crashes on no blanks, the bulk delete that leaves #REF! behind. None of them raise an obvious error — they just produce slightly wrong data.

ExcelMaster lets you describe the outcome instead. Say "delete every row where column A is empty" or "remove rows where status is Cancelled," and it writes the loop in the safe direction (or a Union/filter for large data), targets .EntireRow, guards the edge cases, and backs up the sheet first. You keep the workbook and the code; you skip the part where a missed Step -1 quietly corrupts a report.

Frequently asked questions

Why does my VBA loop skip rows when deleting?

Because deleting a row slides every row below it up by one, but a forward loop keeps incrementing. After you delete row 5, the old row 6 becomes row 5 — and the loop moves on to row 6, skipping it. Loop backwards instead: For i = lastRow To 2 Step -1. The rows you haven't checked are then always above the deletion, so their numbers never move.

How do I delete an entire row in VBA?

Use .EntireRow.Delete — for example ws.Rows(11).EntireRow.Delete or ws.Cells(11, "A").EntireRow.Delete. Both remove the whole row so every column stays aligned. Avoid Cells(11, "A").Delete, which deletes a single cell and shifts only that column, scrambling the rest.

What is the fastest way to delete many rows in VBA?

For hundreds or thousands of rows, don't delete one at a time. Either AutoFilter to the rows you want gone and delete the visible cells in one step, or build a Union of all target rows during the loop and call .EntireRow.Delete once at the end. A single multi-row delete is far faster than many individual ones, especially with ScreenUpdating and Calculation turned off.

How do I delete all blank rows in VBA?

ws.Range("A2:A" & lastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete removes every row whose column A is blank in one operation. Wrap it in On Error Resume Next / On Error GoTo 0 because SpecialCells raises an error when there are no blank cells to find.

Why do I get #REF! errors after deleting rows?

Deleting rows shifts cell references. Formulas that point past the deleted rows are renumbered automatically, but any formula that referenced a cell inside a deleted row can no longer resolve and becomes #REF!. Before bulk-deleting, check whether other cells depend on the rows you're about to remove.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-07.