Excel VBA Delete Row: Methods, Examples, and Best Practices

To delete a worksheet row with Excel VBA, reference the worksheet and call its Rows collection’s Delete method—for example, ws.Rows(5).Delete deletes row 5 on the worksheet assigned to ws. For a macro that removes rows by a condition, loop from the bottom of the data upward so that deleting one row does not cause the next row to be skipped.

Delete a specific worksheet row

In Excel for Microsoft 365 on Windows, a worksheet row can be deleted with the Delete method. The simplest form is Rows(5).Delete, but it is safer to qualify the row with a worksheet variable. Otherwise, VBA may apply the command to whichever sheet is active when the macro runs. Assign the intended sheet explicitly, as in this example:

Sub DeleteSpecificRow()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ' Delete row 5 on the Data worksheet.
    ws.Rows(5).Delete
End Sub

This code deletes the fifth row of the worksheet named Data in the workbook containing the macro. Change the sheet name and row number to match your file. If the sheet name does not exist, VBA raises an error rather than silently choosing a different sheet. The row number is a worksheet position, not a record number: if the first row contains headers, row 5 is the fourth data row.

You can also specify a row range, such as ws.Rows("5:7").Delete, to delete three consecutive worksheet rows. Deleting rows shifts the rows below them upward; it does not leave empty rows behind. In current Excel worksheets, row numbers run from 1 to 1,048,576, but a row number must still be within the sheet’s available range.

Delete a whole row or only some cells?

One common source of mistakes is confusing deletion of a worksheet row with deletion of a cell or range. ws.Rows(5).Delete removes the entire fifth worksheet row, including its contents and formatting across the sheet. That is usually the right operation when each row represents one complete record, such as an order or a customer.

By contrast, deleting a cell range can move other cells and leave the rest of a record misaligned. For example, ws.Range("A5").Delete deletes one cell and Excel needs to shift cells up or left. A range deletion can specify the direction explicitly, such as ws.Range("A5:A10").Delete Shift:=xlShiftUp. Use that approach only when you intend to close a gap within a particular column or range, not when you mean to remove complete records.

When you want to make the intent especially clear, use ws.Rows(5).EntireRow.Delete. For a row range, ws.Range("5:7").Delete is another way to express the operation. Whichever syntax you choose, qualify it with the worksheet to avoid relying on the active sheet.

Delete rows based on a condition

For repeated cleanup, a macro can inspect a column and delete every row that meets a rule. This example assumes row 1 contains headings, column C contains status text, and the data occupies the rows below the heading. It removes rows whose status is Closed, ignoring differences in capitalization:

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

    ' Work upward so row deletions do not skip records.
    For r = lastRow To 2 Step -1
        If StrComp(CStr(ws.Cells(r, "C").Value), _
                   "Closed", vbTextCompare) = 0 Then
            ws.Rows(r).Delete
        End If
    Next r
End Sub

The last-row calculation starts at the bottom of column C and moves upward to its last nonempty cell. The loop stops at row 2 to preserve the heading. The comparison converts the cell value to text and uses vbTextCompare, so Closed, CLOSED, and closed match. This example assumes the status cells do not contain Excel error values; handle those separately if errors are possible.

The downward direction is important. If you loop from row 2 upward and delete a matching row, the following record moves into the row just examined. Advancing the loop can then skip that moved record. Starting at the last row and moving toward the top avoids that shift-related problem. Adjust the comparison, column, and starting row for your layout; if your data has no header, for instance, the loop can start at row 1.

Choose a method for the data layout

A fixed row number is suitable when the target is known and stable, such as removing a temporary instruction row. A condition-based loop is more appropriate when the records change over time. Before writing a deletion macro, decide what defines a record, which column contains the test value, and whether a header or totals row must be preserved.

One known row: use a qualified statement such as ws.Rows(12).Delete.
Several adjacent rows: delete them as a block with ws.Rows("12:15").Delete.
Rows matching a rule: inspect the relevant column and delete from the bottom upward.
An Excel table: consider deleting a table data row through the table’s ListRows collection so the operation is expressed in table terms.

With a table named SalesTable, for example, ws.ListObjects("SalesTable").ListRows(3).Delete deletes its third data row, not the worksheet’s third row. Table headers and totals are not counted as data rows in that collection. This distinction matters when a table begins well below the top of a sheet or grows as records are added.

For large sets of records, deleting one row at a time may take longer because Excel has to update the sheet repeatedly. Filtering the data and removing a block of visible records can be more efficient, but it requires careful handling of hidden rows and headers. Choose the method that makes the target rows unambiguous, not merely the one with the shortest code.

Filtered rows, tables, and hidden data

Filtering does not delete records; it only hides rows that do not meet the filter. A macro that loops through every worksheet row can still encounter hidden rows, so a condition-based loop may delete matches that are not currently visible. Decide whether the intended target is every matching record or only the records visible after filtering.

Deleting visible rows alone requires an approach that explicitly selects visible cells, and care is needed to avoid including the heading or deleting hidden records unintentionally. A safer workflow is to confirm the filter and target range first, then remove only the intended data rows. For a structured Excel table, use its data rows rather than assuming worksheet row numbers correspond to table positions.

Also check for merged cells, protected sheets, and formulas that refer to the rows being removed. A protected worksheet may reject the deletion unless the macro has the necessary permissions or the sheet is unprotected first. Deleting a row can change formula results and references elsewhere in the workbook. Excel often adjusts direct references when rows are removed, but that does not guarantee that every downstream calculation or external process still has the meaning you intended.

Safety before running a deletion macro

Row deletion is a destructive operation. Save a separate copy of the workbook before running a macro, especially when it processes many records or uses a condition. VBA changes generally cannot be reversed with Excel’s Undo command (Ctrl+Z on Windows). Closing without saving may sometimes let you discard changes, but it is not a substitute for a backup.

Confirm the workbook and worksheet references point to the intended file and sheet.
Check whether the macro preserves headings, totals, and any rows that should not be removed.
Run a test on a copy with a small, recognizable sample of records.
Review the criteria carefully, including blank cells, spelling, and data types.
Save the workbook after checking the results, and keep the original copy until the outcome is verified.

For an especially consequential cleanup, first use a non-deleting version of the loop that counts or marks matching rows. Comparing that count with the number of records you expect to remove can reveal an overly broad condition before it changes the sheet. Another cautious option is to copy matching records to a review sheet before deleting them from the source.

Macros require a macro-capable workbook format such as .xlsm if you need to retain the VBA code. Excel’s macro security settings may also block a macro until the file is trusted or the code is enabled. Do not enable macros in an unfamiliar workbook merely to delete rows; inspect its source and code first.

Common errors and how to avoid them

If the wrong sheet changes, the code probably used an unqualified Rows reference or relied on the active sheet. Use ws.Rows(r).Delete and set ws to the intended worksheet. If the macro reports that a subscript is out of range, check that the workbook contains a worksheet with exactly the name used in Worksheets("Data").

If some matching rows remain, check the loop bounds and the condition. The example starts at row 2 because it assumes a header in row 1; a different layout needs a different starting row. Also check for extra spaces, unexpected values, or formulas that return text. The example comparison ignores capitalization, but it does not trim leading or trailing spaces, so a cell containing Closed may not match.

If records are skipped, confirm that the loop runs from the last row to the first. If deleting rows is unexpectedly slow, avoid repeatedly deleting individual rows across a very large data set; consider filtering and deleting a verified block or using a table-aware method. A protected sheet, merged cells, or an incorrect worksheet reference can also prevent an otherwise valid deletion from working as expected.

Alternatives to VBA

VBA is useful when deletion is a repeatable part of a workflow, but it is not necessary for a one-time cleanup. You can select worksheet rows and use the row context menu’s Delete command, or use Home > Delete > Delete Sheet Rows. On Windows, select a cell in the row and press Ctrl+- (Ctrl and the minus key); Excel displays deletion choices when the selection does not already identify whole rows. On Mac, the equivalent shortcut is Control+-; keyboard layouts may require a different key combination to enter the minus sign.

If you only need to exclude records from a report, filtering may be safer than deleting the source data. If you need a repeatable transformation without VBA, Power Query can filter rows during a query and load the resulting data separately. These alternatives preserve the original records more readily than a destructive macro, although they solve different problems: a filter hides records, and a query produces transformed output rather than directly deleting source rows.

In Excel for the web, VBA macros cannot be created, edited, or run. Use the desktop Excel application for VBA. Excel for Mac supports VBA in the desktop app, though ribbon and keyboard details can vary with the installed version and keyboard settings. The worksheet deletion logic itself is the same, but do not expect a VBA macro to execute in the browser.

Frequently Asked Questions

How do I delete an entire row in Excel VBA?
Use a worksheet-qualified statement such as ws.Rows(5).Delete. It removes the complete fifth worksheet row and shifts rows below it upward.

How do I delete multiple rows in VBA?
For adjacent rows, use a range such as ws.Rows("5:8").Delete. For rows that meet a condition, loop through the data from the last row upward and delete each matching row.

How can I delete a row if a cell contains specific text?
Test the relevant cell in a loop, then delete its worksheet row when the test matches. Start at the bottom and move upward; this prevents rows shifting into an unvisited position and being skipped.

Why does my VBA macro delete the wrong row?
Check whether the row number is a worksheet position or a position within an Excel table, and confirm the worksheet is explicitly referenced. An unqualified Rows(5) acts on the active sheet, which may not be the sheet you intended.

Can I undo a row deletion made by VBA?
Usually not with Ctrl+Z. Save a copy before running a deletion macro, then verify the result before saving over the original workbook.

Does VBA work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA.

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.