VBA Code to Delete Rows in Excel: Practical Examples

In Excel VBA, delete a worksheet row with Rows(rowNumber).Delete; when removing rows that meet a condition, loop from the bottom upward so deleted rows do not make you skip the next record. For example, this macro removes rows where column C says Closed, while keeping row 1 as a header:

Sub DeleteClosedRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' Start at the bottom so deleting a row does not shift untested rows.
    For r = lastRow To 2 Step -1
        If ws.Cells(r, "C").Value = "Closed" Then
            ws.Rows(r).Delete
        End If
    Next r
End Sub

Change the worksheet name, column, comparison value, and starting row to fit your data. Save a copy before running any deletion macro: VBA changes cannot be reversed with Ctrl+Z on Windows or Command+Z on Mac.

How the row-deletion code works

ws.Rows(r).Delete deletes the entire worksheet row whose number is stored in r. That is different from clearing its values: deletion shifts cells below upward, while clearing leaves an empty row in place. In the example, the worksheet is explicitly assigned to the Data tab, and lastRow finds the last nonempty cell in column C.

The loop begins at lastRow and counts down to row 2. If it ran upward, deleting row 2 would move the old row 3 into row 2; the loop would then advance to row 3 and miss the shifted record. Counting backward avoids that problem. The starting number 2 protects a header in row 1, but change it if your headers or data start elsewhere.

The final used row is determined from column C in this sample. That is suitable when every relevant record has a value in the status column. If column C can be blank while other columns still contain data, determine the last row from a reliably populated column instead, or find the last used cell across the worksheet. An incorrect last-row calculation can leave records unexamined.

Set up and run a macro

VBA runs in the desktop versions of Excel, not Excel for the web. In Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. If the Developer tab is visible, Developer > Visual Basic opens the same editor. On Mac, use Tools > Macro > Visual Basic Editor; the keyboard shortcut may depend on the keyboard’s function-key settings.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the macro.
Check the worksheet name and the row and column references in the code. Use a copy of the workbook or a small test dataset first.
Return to Excel and run the procedure with Alt+F8 on Windows, select its name, and choose Run. On Mac, open the Macro dialog from Tools > Macro > Macros and run it there.
Inspect the results and save only when they are correct. Do not enable macros in an unfamiliar workbook just to run code you have not reviewed.

Excel may display a security warning when opening a workbook containing macros. Only enable content for a workbook and source you trust. A macro-enabled file can contain code that changes or deletes workbook data, so its file extension is not a guarantee of safety.

Delete rows that contain blank cells

To remove records where a required field in column A is blank, test that field and still loop upward from the last record. This version treats a formula returning an empty string as blank too, because its displayed value has zero length:

Sub DeleteRowsWithBlankID()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Row 1 is assumed to contain headings.
    For r = lastRow To 2 Step -1
        If Len(Trim$(CStr(ws.Cells(r, "A").Value))) = 0 Then
            ws.Rows(r).Delete
        End If
    Next r
End Sub

This tests only column A. A row with a blank ID but useful information elsewhere will still be deleted, which may or may not be intended. If you mean a row is blank only when several fields are empty, test those fields together rather than relying on one column. Also decide how to handle cells containing spaces: Trim$ treats ordinary leading and trailing spaces as insignificant.

Delete rows using a date condition

A date condition works the same way as a text condition: compare a field, then delete matching rows. This example removes records dated before January 1, 2024, where dates are in column B and row 1 contains headings:

Sub DeleteOldRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim cutoff As Date

    Set ws = ThisWorkbook.Worksheets("Data")
    cutoff = DateSerial(2024, 1, 1)
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Delete only cells Excel recognizes as dates before the cutoff.
    For r = lastRow To 2 Step -1
        If IsDate(ws.Cells(r, "B").Value) Then
            If CDate(ws.Cells(r, "B").Value) < cutoff Then
                ws.Rows(r).Delete
            End If
        End If
    Next r
End Sub

DateSerial(2024, 1, 1) avoids ambiguity between regional date formats such as month/day/year and day/month/year. The check skips blanks and values VBA does not recognize as dates. Text that looks like a date can be interpreted according to regional settings, so convert or validate imported date text before relying on a comparison.

Delete records from an Excel table

If the data is an actual Excel table created with Insert > Table, deleting table records through the table’s own rows collection can be clearer than deleting worksheet rows. It keeps the operation focused on the table and does not remove unrelated cells elsewhere on the sheet. In this example, the table is named Orders, and its third table column contains statuses:

Sub DeleteClosedTableRecords()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("Orders")

    ' Table row 1 in DataBodyRange is a data row, not the header.
    For r = tbl.ListRows.Count To 1 Step -1
        If tbl.ListRows(r).Range.Cells(1, 3).Value = "Closed" Then
            tbl.ListRows(r).Delete
        End If
    Next r
End Sub

The number 3 refers to the position of the status field within the table, not necessarily worksheet column C. Update it if the table’s column order changes. A table with no data rows has no records to inspect; in that case the loop simply has no iterations. If your code needs to access a table’s data body separately, account for the possibility that the table is empty.

Whole worksheet rows or only selected cells?

Choose the deletion scope deliberately. ws.Rows(r).Delete removes the whole worksheet row, including values outside the list you are cleaning. That may be correct for a sheet dedicated to one dataset, but it can damage notes or a separate report placed to the right of the data. For a table, deleting a ListRow is usually more contained.

Deleting a range such as ws.Range("A" & r & ":C" & r).Delete Shift:=xlUp removes only those cells and shifts cells in the specified range upward. It can misalign columns if the surrounding data is meant to remain one record per row. Use it only when shifting that range independently is intentional. Clearing cells with ClearContents preserves the row structure but leaves formatting and an empty record location.

Common errors and safer habits

Rows are skipped: the loop is probably counting upward while deleting. Reverse the loop, or collect row numbers and delete them from the bottom up.
The header disappears: the loop starts at row 1. Start at the first data row, commonly row 2, and verify the sheet’s actual layout.
The wrong sheet changes: unqualified references such as Rows(r).Delete act on the active sheet. Qualify references with a worksheet variable, as the examples do.
Some records remain: the chosen last-row column may contain gaps, or text may not match exactly because of extra spaces, spelling differences, or different capitalization assumptions.
Other content shifts or disappears: a full worksheet-row deletion affects every column in that row. Check for formulas, notes, or other data outside the main list.

For large datasets, deleting one row at a time can be slow because Excel repeatedly updates the sheet. A filter-based approach or a method that builds a cleaned output range may be faster for very large lists, but those approaches need careful handling of headers, hidden rows, and formulas. For a modest list, a clear bottom-up loop is often easier to inspect and maintain.

Deleting filtered or visible rows

A filter hides records; it does not delete them. If a macro is meant to remove only visible filtered results, a plain loop through every row can also reach hidden records unless the code explicitly checks visibility. Conversely, selecting visible cells and deleting entire worksheet rows can affect data beyond the filtered table. Confirm whether the goal is to remove all matching records or only currently visible matches, then test the macro on a copy.

For a one-time cleanup, Excel’s filter interface may be simpler: filter the target field, select the displayed data rows without the header, and delete the rows using the worksheet row context menu. Verify the selection first, especially if the sheet contains unrelated data. A reusable macro is useful when the same rule must be applied repeatedly, but repeatability makes it more important to document the target sheet and criteria.

Frequently Asked Questions

What is the VBA command to delete a row in Excel?
Use Rows(5).Delete to delete worksheet row 5 on the active sheet, or qualify it as ws.Rows(5).Delete to target a specific worksheet. A qualified reference is safer in reusable macros.

How do I delete rows based on a cell value?
Check the relevant cell inside a loop and call Delete when it matches the criterion. Loop from the last data row upward, and set the starting row so the header is not tested or deleted.

Can I undo a VBA row deletion?
Usually not with Excel’s normal Undo command. Save a backup or copy before running deletion code; if the macro has just run and the workbook is still open, close without saving only if you are willing to discard all changes made since the last save.

Can VBA macros run in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the Excel desktop application to run VBA.

Should I delete an entire row or clear its contents?
Delete a whole row when the record should be removed and cells below should shift up. Clear contents when the row position must remain, or delete a table row when the records belong to an Excel table and unrelated worksheet cells should stay untouched.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.