Delete Entire Row VBA: Examples and Safety Tips

To delete a complete worksheet row with VBA, use Rows(rowNumber).Delete on a specifically named worksheet, such as Worksheets("Data").Rows(5).Delete. This removes row 5 and shifts the rows below it upward; save a copy first, because deleting rows with a macro cannot be undone with Ctrl+Z.

What “delete an entire row” means in VBA

A worksheet row spans every column in the worksheet, not just the cells that currently contain data. Deleting it removes that row’s cells, formatting, and row-level properties, then moves the rows beneath it up. That differs from clearing cells: a clear operation leaves the row in place, while deleting a row changes the layout of the worksheet.

VBA offers a few ways to express the same basic operation. Rows(5).Delete deletes worksheet row 5 when it is applied to a worksheet. Range("B5").EntireRow.Delete starts with a cell reference and deletes the whole row containing that cell. The second form is useful when your code already has a range or cell reference, but the first is often easier to read when you know the row number.

These commands delete a worksheet row, not merely the values in a table record. If your data is an Excel table and you intend to remove only one table record, use the table’s row-deletion method instead; that distinction is covered below.

Delete one row safely

Put the following macro in a standard VBA module. It explicitly identifies the workbook containing the code and the worksheet to change, rather than relying on whichever workbook or sheet happens to be active.

Sub DeleteOneWorksheetRow()
    Dim ws As Worksheet

    ' Target the worksheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Delete worksheet row 5.
    ws.Rows(5).Delete
End Sub

Replace Data with the worksheet’s tab name and change 5 to the row number you intend to remove. The name must match the sheet tab, including spaces. For example, a sheet named “Monthly Report” would be referenced as ThisWorkbook.Worksheets("Monthly Report").

ThisWorkbook means the workbook that holds the VBA code. That is usually the right choice when the macro is stored in the workbook it edits. ActiveWorkbook means whichever workbook is currently active in Excel. It can be appropriate for a macro designed to work on the user’s selected workbook, but it can also target the wrong file if another workbook is active. Qualifying the worksheet, as in ws.Rows(5).Delete, also prevents the macro from depending on the active sheet.

How to run the macro in Excel

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the macro, and change the worksheet name and row number as needed. Return to Excel and use Alt+F8 to open the Macro dialog, select DeleteOneWorksheetRow, and choose Run. You can also run a macro from the Visual Basic Editor with the insertion point inside its procedure by pressing F5.

Save the workbook in a macro-enabled format such as .xlsm if you need to keep the VBA code. Excel may display a security notice when a workbook with macros is opened; enable macros only for files you trust. On a Mac, the Visual Basic Editor and macro tools are available in Excel, but keyboard shortcuts can vary with the keyboard and macOS settings. Use Excel’s Developer tab or menu commands if a Windows shortcut does not work. Excel for the web can open a macro-enabled workbook, but it does not run VBA macros; use the desktop Excel app.

Before running any deletion macro, save a separate copy of the workbook or make another backup. VBA actions generally cannot be reversed with Excel’s Undo command, and pressing Ctrl+Z after a macro will not restore the deleted row.

Delete rows that meet a condition

A common task is removing records that match a condition, such as rows marked “Closed” in column C. When deleting multiple rows in a loop, work from the bottom upward. If you start at the top, deleting a row shifts the next record into the row just processed; the loop can then skip that record as it advances.

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

    ' Set the worksheet and find the last used row in column C.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' Start below the header and delete matching rows upward.
    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

This version treats row 1 as a header and checks column C. Adjust the starting row if your headers are elsewhere, and change the condition and column to match your data. If the matching text may contain extra spaces or different capitalization, define how those values should be treated before deleting; a simple equality comparison does not remove leading or trailing spaces from the cell value.

The macro finds its last row by looking upward from the bottom of column C. That is suitable when column C is reliably filled for every record. If the column can be blank in the final records, the detected last row may be too early. Choose a column that is populated throughout the data, or calculate the last used row using a method suited to the worksheet’s layout. Test the logic on a copy, especially when the condition could match many records.

Deleting several known rows

For a short, fixed set of adjacent rows, a single range deletion is straightforward. For example, ws.Rows("5:7").Delete removes worksheet rows 5 through 7 in one operation. To remove separated row numbers, deleting one at a time from highest to lowest avoids changing the positions of the lower-numbered rows before they are processed.

Suppose you want to remove original rows 4, 9, and 12. Delete row 12 first, then row 9, then row 4. If row 4 were deleted first, the original row 9 would become row 8, and the original row 12 would become row 11. A loop over a stored list of row numbers should therefore sort them in descending order before deleting, or use a bottom-up scan such as the conditional example above.

For a large number of matching records, deleting rows individually can take time because Excel has to update the worksheet repeatedly. A more advanced macro may collect matching rows and delete them together, but the correct approach depends on whether rows are contiguous, filtered, or part of a table. Do not combine ranges casually: multi-area ranges and deletion behavior can be less obvious than a simple bottom-up loop. For a small or moderate data set, the clear loop is often easier to check and maintain.

Worksheet rows versus Excel table rows

An Excel table, created with Insert > Table, has its own data rows and structured references. Deleting an entire worksheet row can remove cells beyond the table as well, affecting other content on that same row. If the goal is specifically to remove the third data record from a table named SalesTable, use the table’s ListRows collection:

Sub DeleteThirdTableRecord()
    Dim ws As Worksheet
    Dim lo As ListObject

    ' Find the table on the named worksheet.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")

    ' Delete the third data row only if it exists.
    If lo.ListRows.Count >= 3 Then
        lo.ListRows(3).Delete
    End If
End Sub

In this example, ListRows(3) means the third data row within the table; the table header is not counted as a data row. This method is more precise when the intended change is to the table record rather than the worksheet’s whole row. If a table contains totals or other worksheet content beside it, check the result on a copy to confirm the operation matches your layout.

Delete, clear, or hide: choosing the right action

Deleting is appropriate when the row should be removed and the remaining worksheet content should move up. It can affect formulas, named ranges, charts, and other references, so inspect dependent calculations after a macro runs. Excel often adjusts formulas when cells are deleted, but the outcome depends on how the formulas and references are constructed; do not assume every downstream report will remain correct.

Delete the row when the record should no longer occupy a worksheet row and later rows should shift upward.
Clear values when you want to keep the row, its formatting, or its position. A VBA statement such as ws.Rows(5).ClearContents removes contents but does not delete the row.
Hide the row when you want to keep the data available but temporarily out of view. Hiding is reversible and does not shift other rows.
Delete a table row when you want to remove a record from an Excel table without treating the entire worksheet row as the target.

Removing a row is not the same as filtering it out of view, and clearing a row is not the same as deleting it. Choose based on whether you need to preserve the record, the worksheet layout, or both.

Common errors and how to prevent them

A “subscript out of range” error commonly means the workbook does not contain a worksheet with the name supplied in the code. Check the spelling and spaces on the sheet tab. An error involving an invalid object can also arise if the workbook or worksheet reference was not set as expected. Naming the workbook and worksheet explicitly makes the target easier to verify.

A macro that deletes the wrong row often has an indexing problem rather than a VBA syntax problem. Check whether row 1 is a header, whether your loop runs upward or downward, and whether the row numbers refer to the current sheet after earlier deletions. For conditional deletion, bottom-up iteration is the usual safe pattern. Also confirm that the checked column is the one containing the intended criteria and that blank or unexpected values will not be treated as matches.

If the macro runs without an error but appears to do nothing, check that the target workbook is the one containing the expected data and that the condition is an exact match. A cell may contain a formula result, extra spaces, or a different spelling. For a filtered list, decide whether you mean every matching worksheet record or only rows currently visible after filtering; those are different tasks and need deliberate handling. Always inspect a backup or test copy before applying a deletion to important data.

Frequently Asked Questions

What is the simplest VBA code to delete row 5?
Use Worksheets("Data").Rows(5).Delete after qualifying the workbook or worksheet appropriately. Replace Data with the actual sheet name, and confirm that row 5 is the row you intend to remove.

How do I delete a row based on a cell value?
Loop through the data from the last relevant row upward, test the desired cell, and call ws.Rows(r).Delete when the condition is true. The bottom-up direction prevents row shifts from causing the loop to skip records.

Can I undo a row deleted by VBA?
Usually not with Ctrl+Z. Macros clear or replace Excel’s normal Undo history, so save a separate copy before running code that deletes data.

Does Rows(5).Delete clear the row or remove it?
It removes the worksheet row and shifts rows below it upward. To keep the row in place while removing its values, use a clearing method such as ClearContents.

How do I delete a row from an Excel table only?
Reference the table as a ListObject and delete a data row through its ListRows collection, for example lo.ListRows(3).Delete. This targets the table record rather than expressing the action as a whole worksheet row deletion.

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.