Excel VBA Delete Whole Row: Safe Methods and Examples

To delete an entire worksheet row with VBA, use Rows(row_number).Delete, such as Rows(5).Delete for row 5. In a macro, qualify the row with its worksheet—ws.Rows(5).Delete—so Excel does not remove a row from whichever sheet happens to be active.

The simplest way to delete a worksheet row

The Rows collection represents complete rows on a worksheet. Calling its Delete method removes the selected row and shifts the rows below it upward. For example, the command Worksheets("Data").Rows(5).Delete deletes row 5 from the sheet named Data.

A standalone procedure with an explicit worksheet reference looks like this. Before running any macro that deletes data, save a separate copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

Sub DeleteOneRow()
    ' Delete row 5 on the Data worksheet.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")

    ws.Rows(5).Delete
End Sub

ThisWorkbook means the workbook containing the macro. If the macro is stored in another workbook, such as the Personal Macro Workbook, it may not be the workbook you intend to edit; reference the target workbook deliberately. The sheet name in the example must also match the tab name exactly.

You can delete a block of consecutive rows with a row address: ws.Rows("5:7").Delete removes rows 5 through 7. Excel shifts the remaining rows upward once. The same operation can be written with a range: ws.Range("5:7").Delete, though Rows makes the intention particularly clear.

How to put the macro in Excel

These instructions apply to desktop Excel for Microsoft 365 on Windows. Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code in that file. A standard .xlsx workbook cannot store VBA macros.

Save a backup copy of the workbook before testing a deletion macro.
Open the VBA editor with Alt+F11. If the Developer tab is visible, you can also choose Developer > Visual Basic.
In the editor, choose Insert > Module, then paste the procedure into the new module.
Return to Excel, choose Developer > Macros, select the procedure, and choose Run. You can also run it from the VBA editor.

On a Mac, the VBA editor shortcut is commonly Option+F11; some keyboards require Fn as well. You can also use the Visual Basic command on the Developer tab. The VBA language is broadly similar, but Excel for the web does not run VBA macros. Open the workbook in the desktop application to use this code.

Delete rows based on a value

A common task is removing every record whose status, category, or other field matches a condition. When deleting rows inside a loop, work from the bottom toward the top. If you loop downward and delete row 4, the old row 5 moves into row 4; advancing the loop to row 5 would skip that moved record.

This example assumes row 1 contains headings and column A contains status values. It removes rows where the status is exactly Closed, ignoring any row above the heading.

Sub DeleteClosedRows()
    ' Remove matching records from the Data worksheet.
    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

    For r = lastRow To 2 Step -1
        If ws.Cells(r, "A").Value = "Closed" Then
            ws.Rows(r).Delete
        End If
    Next r
End Sub

The last-row calculation starts at the bottom of column A and moves upward to its last non-empty cell. Consequently, it is appropriate only if column A is populated for every record you need to examine. If column A can be blank while another column is reliably filled, base the calculation on that other column instead. If the data has no records, the loop simply does not run because its starting row is above row 2.

To match text without regard to capitalization, VBA’s comparison behavior can depend on the module’s comparison setting. If capitalization matters, explicitly use a comparison such as StrComp with vbTextCompare; decide whether leading or trailing spaces should count as a match as well. For more involved rules, calculate the condition separately and test it before deleting.

Delete a known row or a changing selection

If the row number is stored in a variable, use that variable with the worksheet’s Rows collection. For example, after validating a value called rowToDelete, the deletion statement is ws.Rows(rowToDelete).Delete. Check that the number is within the worksheet’s row limits and is not a heading row you need to preserve.

To delete a row based on the active cell, a macro can refer to ActiveCell.EntireRow.Delete. This is convenient for a one-off action, but it depends on the user’s current selection and active sheet. A safer version captures and validates the target worksheet and row, especially in a macro that also opens other workbooks or changes sheets.

Use EntireRow.Delete when you start from a cell or a range and mean to remove the full worksheet row. For example, ws.Range("B5").EntireRow.Delete deletes worksheet row 5, not just the cell in column B. By contrast, ws.Range("B5").Delete deletes a cell and asks Excel to shift neighboring cells; it is not the same operation.

Whole worksheet rows versus table records

An Excel table, created with Insert > Table, has its own data rows inside a structured range. Deleting a worksheet row with ws.Rows(5).Delete removes the whole worksheet row, which can affect cells and other content outside the table too. If the goal is to remove just a table record, use the table’s ListRows collection instead.

For instance, a table named Orders can have its third data record removed with ws.ListObjects("Orders").ListRows(3).Delete. The index refers to the table’s data rows, not the worksheet row number; the table header is not counted as a data row. This distinction matters when the table begins several rows down the sheet or when it has a totals row.

Use whole-row deletion when the entire worksheet row is obsolete. Use a table-row deletion when only one record in a table should disappear and other worksheet content should stay in place. Confirm that formulas, charts, named ranges, and other tables behave as intended after either operation.

Common problems and safeguards

Wrong sheet: Unqualified code such as Rows(5).Delete acts on the active sheet. Assign a worksheet object and use ws.Rows(5).Delete to make the target explicit.
Skipped matches: Deleting while looping from top to bottom can skip adjacent matching rows. Count downward, as in the example, or collect row numbers and delete them carefully.
Deleted heading: A loop that starts at row 1 may remove the column headings. Start at the first data row, commonly row 2, when the first row is a header.
Wrong last-row column: A last-row calculation based on a column with gaps may stop too early. Choose a column that is consistently populated for all records being checked.
Merged cells or protected sheets: Merged areas and worksheet protection can prevent a deletion or produce unexpected results. Review the sheet structure and protection settings before running the macro.
Filtered or hidden rows: A filter changes which records are visible, but a macro that loops through worksheet row numbers may still examine hidden records. Define whether the task concerns all records or visible records only before deleting.

For especially valuable data, add a confirmation step or write the rows marked for deletion to a separate sheet before removing them. A preview gives you a chance to verify the matching rule, which is often more important than the deletion command itself. Do not rely on Excel’s undo history: after a VBA macro changes the workbook, Ctrl+Z generally cannot restore the deleted rows.

Choosing between VBA and manual deletion

VBA is useful when the same deletion must be repeated, when the condition spans many rows, or when a larger process needs to remove records automatically. It also makes the rule reusable, but a mistake can affect many rows quickly, and macros require a trusted workbook and the desktop Excel application.

For a small, one-time cleanup, selecting row headers and using Home > Delete > Delete Sheet Rows may be simpler. For records that meet a visible filter, use care to select only the intended visible result rows and verify the selection before deleting. Another cautious approach is to copy matching records to a review sheet, inspect them, and then remove them. Neither method substitutes for a backup when the data matters.

There is an important misconception to avoid: clearing a row’s contents is not deleting the row. The Clear command removes values or formatting while leaving the worksheet row in place. A whole-row deletion shifts rows below upward and can change references and layout. Choose based on whether the row itself should disappear or whether its cells should merely become empty.

Frequently Asked Questions

What is the VBA code to delete row 2?

Use Worksheets("Data").Rows(2).Delete to delete row 2 on the Data worksheet. In a procedure, assign that worksheet to a declared Worksheet variable and use the variable to make the target explicit.

How do I delete multiple entire rows in VBA?

For consecutive rows, use a row range such as ws.Rows("4:8").Delete. For rows that meet a condition, loop through the data from the last row upward and delete each matching row, rather than deleting while counting upward.

Does Rows.Delete delete only the cells with data?

No. ws.Rows(5).Delete deletes the whole worksheet row, including cells across the row, and shifts rows below upward. To clear values without removing the row, use a clearing operation instead; to delete only a cell, use a cell range’s Delete method and specify the desired shift behavior.

Can I undo a VBA row deletion with Ctrl+Z?

Usually not. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z should not be treated as a recovery plan. Save a separate copy before running deletion code, or have the macro create a recoverable copy of affected data.

Can Excel for the web run a VBA macro?

No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA macros. Use desktop Excel for Microsoft 365 on Windows or Mac to execute VBA.

Before you run a deletion macro

Confirm the workbook and worksheet, check the row boundaries, and test the condition on a disposable copy. Then verify the number and identity of the rows removed before using the macro on the original data. Those checks make a short command like ws.Rows(r).Delete far more dependable in real workbooks.

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.