VBA Code to Delete a Row in Excel

To delete a worksheet row with VBA, use Rows(5).Delete for row 5. In a macro, qualify the row with the worksheet you intend to change, such as ws.Rows(5).Delete, so Excel does not delete a row from whichever sheet happens to be active.

Basic VBA code for deleting a specific row

This macro deletes row 5 on the worksheet named Data in the workbook containing the macro. Change the worksheet name and row number to fit your file.

Sub DeleteSpecificRow()
    Dim ws As Worksheet

    ' Save a copy first; VBA deletions cannot be undone with Ctrl+Z.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Rows(5).Delete
End Sub

Rows(5) refers to the entire fifth worksheet row, not just the cells containing data. Deleting it shifts the rows below it up by one. The method works in desktop Excel for Microsoft 365 on Windows and in Excel for Mac. Excel for the web does not run VBA macros; open the workbook in the desktop app to use this code.

ThisWorkbook means the workbook where the VBA code is stored. That distinction matters if a macro in one workbook acts on a different workbook that is currently open. If you intentionally want the active workbook instead, you can use ActiveWorkbook, but make sure the correct file is active before running the macro.

Why the worksheet reference matters

A short command such as Rows(5).Delete acts on the active sheet. The active sheet can change when you click another tab, activate another workbook, or run code that switches sheets. A fully qualified reference makes the target clearer and reduces the chance of deleting the wrong row.

For example, ThisWorkbook.Worksheets("Data").Rows(5).Delete identifies both the workbook and sheet directly. The worksheet name in quotation marks must match the tab name exactly. If the name is misspelled or does not exist in that workbook, VBA raises an error rather than deleting a row on a similarly named sheet.

Use ThisWorkbook when the target sheet is in the workbook that holds the macro.
Use ActiveWorkbook only when you deliberately want the workbook currently in focus.
Use a worksheet variable, as in the example, when you want to refer to the same sheet several times in a longer macro.

Delete the row containing the selected cell

If the desired row depends on where the user clicks, use the active cell’s row number. This example deletes the selected row on the active worksheet, but protects row 1 in case it contains column headings.

Sub DeleteActiveWorksheetRow()
    Dim ws As Worksheet
    Dim rowNumber As Long

    ' Save a copy first; VBA deletions cannot be undone with Ctrl+Z.
    Set ws = ActiveSheet
    rowNumber = ActiveCell.Row

    If rowNumber > 1 Then
        ws.Rows(rowNumber).Delete
    End If
End Sub

The row-number check is only a basic safeguard: it prevents deletion of row 1, but it does not confirm that the selected row is safe to remove. If a user selects the wrong cell, the macro will still delete that cell’s row. For a shared workbook, a confirmation prompt can reduce accidental deletions; for a fixed, repeatable task, a named worksheet and explicit row number are usually easier to audit.

Delete rows that match a condition

To remove every record marked Closed in column C while keeping the header in row 1, loop from the bottom of the data upward. This example assumes column C contains ordinary text values and that the Data sheet is the target.

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

    ' Save a copy first; VBA deletions cannot be undone with Ctrl+Z.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    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

The bottom-up direction is important. If you scan from row 2 downward and delete a matching row, the next row moves into its place. Increasing the loop counter then skips that shifted record. Counting down avoids that problem because deleting a row does not change the positions of the rows still to be checked above it.

End(xlUp) finds the last nonempty cell in column C by starting at the bottom of the worksheet and moving upward. Choose a column that is reliably filled for each record. If column C has blanks near the bottom while another column contains records, the macro may stop early and leave some rows untouched. The comparison shown matches the text Closed; values such as closed or Closed with extra spaces may not match as intended.

Delete a range or several matching rows

For a known block of consecutive rows, pass the range of row numbers to the Rows collection. For example, ws.Rows("5:8").Delete removes rows 5 through 8 in one operation. This is clearer and generally more efficient than deleting those four rows separately.

For rows selected by a rule, continue to delete from the bottom up as in the Closed example. If there are thousands of matches, deleting one row at a time can be slower because each deletion shifts worksheet cells. A faster design can identify matching rows first and delete a combined range in batches, but it needs careful handling of discontiguous areas and should be tested on a copy. For routine lists, a simple bottom-up loop is often easier to understand and maintain.

Do not assume that a list of row numbers can be deleted safely from top to bottom. If you delete row 5 first, what was row 10 becomes row 9. Subsequent fixed row numbers may then refer to different records. Deleting in descending order, or deleting one continuous range at once, avoids this shifting issue.

Deleting a table row is different

If the data is formatted as an Excel Table, deleting a whole worksheet row can affect cells outside the table as well. To remove a table data row while preserving the table structure, use its ListRows collection. In this example, SalesTable is the table name on the Data worksheet, and the macro deletes its third data row if that row exists.

Sub DeleteThirdTableRow()
    Dim ws As Worksheet
    Dim tbl As ListObject

    ' Save a copy first; VBA deletions cannot be undone with Ctrl+Z.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("SalesTable")

    If tbl.ListRows.Count >= 3 Then
        tbl.ListRows(3).Delete
    End If
End Sub

The table row number counts data rows only; it does not count the header row. Use the table’s actual name, which you can see after selecting the table on the Table Design tab. For ordinary worksheet ranges, use ws.Rows(number).Delete; for a table record, prefer tbl.ListRows(number).Delete.

Delete versus clear contents

Deleting a row removes the entire worksheet row and shifts rows beneath it upward. That can change formulas, references, charts, and the positions of unrelated information lower on the sheet. Before using row deletion, confirm that you really want to remove the record and close the gap.

If you only need to empty values while keeping row positions and formatting, use ClearContents on the relevant range instead. For example, ws.Range("A5:F5").ClearContents removes values and formulas in those cells but leaves the row in place; it does not remove formatting. If the goal is to remove a row from a table, use the table-specific method rather than clearing cells across the worksheet.

A related misconception is that deleting a row is the same as pressing Delete on the keyboard. In Excel, the Delete key normally clears cell contents; it does not delete the worksheet row. VBA’s Rows(...).Delete explicitly removes the row and shifts cells upward.

Run the macro and protect your data

In Excel for Microsoft 365 on Windows, save a macro-enabled workbook as .xlsm, open the VBA editor with Alt+F11, choose Insert > Module, paste the code, and run the procedure with F5 while the cursor is inside it. You can also return to Excel and use Alt+F8 to select a macro. On Mac, the VBA editor and macro dialog have corresponding commands; keyboard shortcuts can depend on the keyboard and function-key settings, so use the Developer tab if a shortcut is intercepted. Excel for the web cannot execute VBA.

Row deletion by VBA cannot be reversed with Ctrl+Z. Save a separate copy before running a macro, especially if it deletes multiple records, and test the code on a sample workbook first. Only enable macros from files and sources you trust. If the macro reports that a worksheet or table cannot be found, check the exact tab or table name and confirm that the code is stored in the workbook referenced by ThisWorkbook.

Frequently Asked Questions

What is the simplest VBA command to delete a row?
Use Rows(5).Delete to delete worksheet row 5 on the active sheet. For safer targeting, qualify it with a worksheet, such as Worksheets("Data").Rows(5).Delete.

How do I delete the row of the active cell?
Use ActiveCell.EntireRow.Delete to delete the row containing the selected cell. Add a check for header rows or other protected rows if the macro should not remove them.

How can I delete rows where a cell contains specific text?
Loop through the relevant rows from bottom to top, test the chosen column’s value, and delete the entire row when it matches. The Closed example above demonstrates this pattern for column C.

Can I undo a row deleted by VBA?
No. Running VBA clears Excel’s normal undo history, so Ctrl+Z will not restore a row deleted by a macro. Save a copy or use a tested backup before running deletion code.

How do I delete a row from an Excel Table?
Use the table’s ListRows(index).Delete method, where the index refers to a data row and excludes the header. This removes a table record without treating the request as a whole-sheet 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.