VBA Delete Whole Row: Safe Methods and Examples

To delete an entire worksheet row with VBA, use Rows(5).EntireRow.Delete, replacing 5 with the row number you want to remove. In a macro, qualify the command with the worksheet, such as ws.Rows(5).EntireRow.Delete, so Excel deletes the row from the intended sheet.

Deleting a row is a structural change: cells below it move up, and Excel adjusts many affected formula references. Before running any deletion macro, save a copy of the workbook; VBA actions cannot be reversed with Ctrl+Z.

What deleting a whole row does

A worksheet row runs from column A through the last column of the sheet, not just through the columns containing visible data. Deleting row 5 therefore removes every cell in that worksheet row, including values, formulas, formatting, notes, and other cell content. Excel shifts the rows below upward to fill the gap.

This is different from clearing cells. If you delete a row because a record should no longer exist, row deletion is usually appropriate. If you want to retain the row layout and simply remove its values, use a clearing method instead. Choosing the wrong operation can shift unrelated data or change formulas elsewhere on the sheet.

The method call is made on a range, so context matters. ws.Rows(5) refers to row 5 on the worksheet stored in the variable ws. By contrast, an unqualified Rows(5) can refer to the active sheet, which may not be the sheet you intended if the user has changed tabs.

Delete one specific worksheet row

For a fixed row number, assign the target worksheet to a variable and call EntireRow.Delete. The following example removes row 5 from a sheet named Data in the workbook that contains the macro. Change the sheet name and row number to suit your workbook.

Sub DeleteOneRow()
    Dim ws As Worksheet

    ' Select the worksheet that contains the row.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Delete the entire fifth worksheet row.
    ws.Rows(5).EntireRow.Delete
End Sub

ThisWorkbook means the workbook containing the VBA code. That is often the right choice when the macro is stored in the workbook it edits. If the code is in Personal.xlsb or an add-in, however, ThisWorkbook may be a different workbook from the one on screen. In that situation, explicitly refer to the target workbook rather than assuming the active workbook is correct.

For a row based on the current selection, you can use ActiveCell.EntireRow.Delete. This acts on the row containing the active cell, so it depends on the selection and active sheet at run time. It is convenient for a user-triggered macro, but less predictable for unattended automation. A safer routine checks the selected range and target worksheet before deleting anything.

Delete rows when a condition is met

To remove rows based on their contents, find the last row to inspect and loop from the bottom upward. Bottom-up order is important: after a deletion, rows below the deleted row shift upward. If you loop from top to bottom, the next row can move into the current position and be skipped when the loop advances.

This example deletes rows from row 2 down when column A is blank, while preserving row 1 as a header. It treats a formula that displays an empty string as blank too. The error check avoids trying to convert an Excel error value to text.

Sub DeleteRowsWithBlankColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim cellValue As Variant

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

    ' Work upward so deletions do not skip the next record.
    For r = lastRow To 2 Step -1
        cellValue = ws.Cells(r, "A").Value2
        If Not IsError(cellValue) Then
            If Len(Trim$(CStr(cellValue))) = 0 Then
                ws.Rows(r).EntireRow.Delete
            End If
        End If
    Next r
End Sub

There is an important limitation in how this example finds the last row: it looks only in column A. If that column can be empty beneath records that contain data elsewhere, it may stop too early. Choose a column that is reliably populated for every record, or calculate the last used row across the relevant data area. Also confirm that row 1 really is the header; change the loop’s starting boundary if your data begins elsewhere.

Delete several rows safely

For a contiguous block, delete the range of row numbers in one operation. For example, after setting ws to the correct sheet, ws.Rows("5:8").Delete removes worksheet rows 5 through 8. Because rows shift up, a later row number may refer to different data after the first deletion. This is another reason to process separate target rows from the bottom up.

When you must examine individual rows, a descending loop is a simple and dependable approach. You can change the test to match a status, date, or other field. For example, to remove records marked Closed in column B, test the cell value in that column inside the loop and delete the whole worksheet row when it matches. Make sure the test handles blank cells and error values in a way that fits the workbook.

For a large dataset, repeatedly deleting rows may be slower than filtering the records and removing the visible matches in a controlled operation. Regardless of the method, check the header row, filters, hidden rows, and the exact range before deleting. A macro that acts on a whole worksheet row can affect data in columns far beyond the table you are looking at.

Worksheet rows and Excel Table records are different

If the data is formatted as an Excel Table, decide whether you want to remove a table record or a worksheet row. Deleting a worksheet row removes that row across the entire sheet, potentially affecting notes or other data placed beside the table. Deleting a table record removes the row from the table and lets the table adjust without asking VBA to delete the entire worksheet row.

For example, this macro deletes table records whose third table column contains Closed. Change the worksheet name, table name, and column number to match your file. It loops backward for the same reason as the worksheet example: removing a record changes the positions of later records.

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

    ' Identify the sheet and Excel Table.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("Orders")

    ' Remove matching table records from bottom to top.
    For i = tbl.ListRows.Count To 1 Step -1
        If tbl.ListRows(i).Range.Cells(1, 3).Value2 = "Closed" Then
            tbl.ListRows(i).Delete
        End If
    Next i
End Sub

The number 3 in this example means the third column within the table, not necessarily worksheet column C. Table headers and structured references can make table-oriented code easier to maintain when columns are rearranged. Use worksheet-row deletion when the entire sheet row should go; use a table’s record-deletion method when only the table entry should be removed.

Delete rows or clear their contents?

Use deletion when the row itself is no longer part of the dataset and you want the rows below to move upward. Use ClearContents when you want to retain the row position and formatting but remove values and formulas. For example, clearing a template input row preserves its borders and layout, while deleting it changes the positions of everything below it.

Clearing contents does not remove formatting, and it does not shift cells. Conversely, deleting an entire row can remove formatting and other cell-level content across the row. If only a particular range should be cleared, target that range rather than the entire row. Be especially careful with sheets where a data table, a summary, and notes share the same row numbers.

Common problems and how to avoid them

The wrong sheet changes: qualify the row with a worksheet variable instead of relying on the active sheet. Check that the sheet name is spelled exactly as it appears on the tab.
Rows are skipped: when a loop deletes matches, count downward with Step -1. Deleting while counting upward changes the positions of rows still to be checked.
The header disappears: set the loop boundary to start below the header, commonly row 2, and confirm where the actual data begins.
The macro fails on a protected sheet: a protected worksheet may prevent deletion. Review the protection setup and permissions before running the routine; do not remove protection casually if the sheet is protected to preserve important controls.
Unexpected neighboring data disappears: EntireRow.Delete affects the whole worksheet row. If the intention is to remove only a table record or clear a few cells, use the narrower operation.
Undo does not restore the data: macros generally clear Excel’s normal undo history. Save a backup copy before running destructive code, and test the macro on sample data first.

Another subtle issue is merged cells. Deleting rows that intersect merged areas can produce errors or unexpected layout changes, especially when the merged region spans multiple rows. Unmerge or redesign the affected area before automating row removal, and test against a copy that includes the same layout features as the real sheet.

A safer workflow before running a deletion macro

Row deletion can be difficult to reverse, so treat the macro like a data-cleanup operation rather than a harmless formatting change. Before using it on a live workbook, take a few practical precautions:

Save a separate copy of the workbook. Ctrl+Z cannot undo a VBA deletion; on Mac, Command+Z is not a reliable way to reverse a macro either.
Test the code on a small copy containing representative cases, including headers, blank cells, formulas, and any relevant errors.
Confirm the target workbook, worksheet, row boundaries, and deletion condition. A one-line condition that is too broad can remove many records.
Run the macro and inspect the rows around the affected area, formulas, tables, and any related summaries.

For repeatable cleanup, it can also help to count or report the rows that match before deleting them. That gives you a chance to confirm the intended number of records, particularly when a macro processes a large worksheet. If the count is unexpected, stop and review the condition before making a structural change.

Frequently Asked Questions

What is the VBA code to delete the active row?
Use ActiveCell.EntireRow.Delete to delete the row containing the active cell. For a reusable macro, explicitly check the active sheet and selection first so it does not delete a row from an unintended worksheet.

How do I delete a row by its number in VBA?
Use ws.Rows(5).EntireRow.Delete after setting ws to the intended worksheet. Replace 5 with the required row number. Qualifying the worksheet avoids relying on whichever sheet happens to be active.

Why should a VBA loop delete rows from bottom to top?
Deleting a row shifts the rows below it upward. A bottom-up loop preserves the positions of the rows the loop has not checked yet, preventing matching records from being skipped.

Does deleting a row with VBA clear only the selected cells?
No. EntireRow.Delete removes the full worksheet row and shifts the rows below upward. To retain the row but remove its values and formulas, use a clearing method such as ClearContents on the intended range.

Can I undo a row deleted by a VBA macro?
Normally, no. Excel’s standard undo command cannot reverse macro actions after the macro runs. Save a copy before running deletion code, and test it on a duplicate workbook first.

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.