Excel VBA: Delete a Range of Rows by Number or Match

To delete worksheet rows with Excel VBA, use Rows("3:7").Delete for rows 3 through 7, or qualify the range with a worksheet, such as Worksheets("Data").Rows("3:7").Delete. For rows selected by a condition, work from the bottom upward so that deleting a row does not cause the next row to be skipped.

What deleting a range of rows means in VBA

VBA can delete entire worksheet rows, or it can delete cells from a range and shift nearby cells. Those operations are not interchangeable. If the goal is to remove records from a table or clear out whole lines of a worksheet, delete entire rows. If only part of a grid should be rearranged, delete a cell range and specify how the remaining cells should shift.

The basic whole-row form is Rows("first:last").Delete. The row numbers are worksheet row numbers, beginning at 1. Qualifying the range with a worksheet is safer than leaving it unqualified: an unqualified Rows refers to the active sheet, which may not be the sheet the macro was intended to change.

For example, a reference to ws.Rows("3:7") means rows 3, 4, 5, 6, and 7 on the worksheet stored in the variable ws. The call to Delete removes them and shifts the rows below upward. It does not merely clear their contents.

Delete a fixed, contiguous range

For a known range of row numbers, qualify the worksheet and call Delete on its Rows collection. This macro removes rows 3 through 7 from a sheet named Data in the workbook containing the macro:

Sub DeleteFixedRows()
    Dim ws As Worksheet

    ' Use the sheet that contains the rows to remove.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Rows("3:7").Delete
End Sub

Change the sheet name and row numbers to suit the workbook. ThisWorkbook means the workbook where this VBA code is stored; it is different from ActiveWorkbook, which means whichever workbook is currently active. If the code is stored in a personal macro workbook or add-in but should edit another workbook, choose and qualify the intended workbook explicitly.

A range expressed as Rows("3:7") is commonly clearer than a cell range followed by EntireRow. Both can delete full rows, but make the intention explicit: the code is removing whole rows, not just the cells in a few columns.

Use variables when row numbers change

If the row boundaries are determined while the macro runs, store them in variables. VBA row numbers should be declared as Long, rather than the older Integer type, because worksheets can contain far more rows than an Integer can represent. Check the bounds before building a row address, especially if the values come from user input or another calculation.

Sub DeleteRowsByNumber()
    Dim ws As Worksheet
    Dim firstRow As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    firstRow = 3
    lastRow = 7

    ' Stop if the requested range is outside the sheet or reversed.
    If firstRow < 1 Or lastRow < firstRow Then Exit Sub
    If lastRow > ws.Rows.Count Then Exit Sub

    ws.Rows(firstRow & ":" & lastRow).Delete
End Sub

This form makes the bounds easy to replace with values read from cells or calculated by other code. It also avoids trying to delete row zero or a range whose end is above its start. When the macro is meant to delete a single row, the same idea works with ws.Rows(rowNumber).Delete.

Delete rows that meet a condition

To remove records based on a value, examine the relevant column and delete matching rows. Iterate from the last candidate row up to the first. Deleting row 10 shifts the old row 11 into position 10; a loop moving downward could then advance past that shifted record. A bottom-up loop avoids that problem.

This example assumes row 1 contains headers, column C contains status text, and every row whose status is Closed should be removed. It compares without regard to capitalization, so Closed and CLOSED both match:

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

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' Keep row 1 as the header and scan records from bottom to top.
    For i = lastRow To 2 Step -1
        If Not IsError(ws.Cells(i, "C").Value) Then
            If StrComp(CStr(ws.Cells(i, "C").Value), _
                       "Closed", vbTextCompare) = 0 Then
                ws.Rows(i).Delete
            End If
        End If
    Next i
End Sub

The last-row calculation looks upward from the bottom of column C to find its last nonblank cell. That is appropriate when column C is populated for every record being considered. If records can have a blank status but data in another column, use a column that reliably marks the end of the data, or calculate the last row from the relevant table instead. The loop protects against error values in the status column, but its exact-match comparison does not remove values such as “Closed – archived.”

Choose between whole rows and shifted cells

Use whole-row deletion when the record spans the sheet and all columns should move together. Use cell deletion only when the data is intentionally arranged in separate blocks and shifting part of the worksheet will preserve its layout.

ws.Rows("3:7").Delete deletes entire rows 3 through 7.
ws.Range("A3:D7").Delete Shift:=xlUp deletes cells A3:D7 and shifts cells below them upward in those columns. Other columns are not necessarily shifted with them.
ws.Rows(3).ClearContents keeps row 3 in place but clears its contents. It is not a deletion.

This distinction matters in lists: removing only A3:D7 from a wider dataset could misalign those fields with columns E onward. Also consider whether formulas, formatting, data validation, or references depend on the rows. Excel adjusts many ordinary cell references when rows are deleted, but the result can still change formulas and workbook structure in ways that require review.

Safety checks before running a deletion macro

Deleting rows is a structural edit, and VBA actions cannot be reversed with Excel’s Ctrl+Z undo command. Save a copy of the workbook before running a macro that deletes data. For a macro used repeatedly, test it on a duplicate file or a small sample and confirm the target sheet, header row, and match rule before applying it to the working workbook.

Useful safeguards include:

Qualify every worksheet and workbook reference instead of relying on whichever sheet is active.
Check row boundaries and define whether the header row must be preserved.
Preview the rows that match a condition before deleting them, particularly when the comparison is broad.
Save the workbook in a macro-capable format such as .xlsm if it needs to retain VBA code.
Keep a separate backup when the macro processes important or difficult-to-recreate data.

For a one-time cleanup, filtering the data and inspecting the visible records first can help verify the selection. For a recurring process, consider adding a confirmation step or writing matching records to a separate sheet before removal. A confirmation prompt reduces accidental runs, but it is not a substitute for a backup.

Common problems and how to avoid them

The macro deletes rows from the wrong sheet. An unqualified Rows reference uses the active worksheet. Set a worksheet variable and use ws.Rows(...) so the target is explicit.

Some matching rows remain. A loop running from the first row downward can skip records after a deletion shifts the next row upward. Reverse the loop, as in the example, or use a carefully configured filter-based method. Also check for leading or trailing spaces and decide whether comparison should be exact or partial.

The header disappears. Check the first row in the loop. Starting at row 1 will include the header; starting at row 2 preserves it only when row 1 really is the header. Adjust the start row for the actual layout.

The wrong cells shift or become misaligned. Check whether the code deletes entire rows or only a cell range. Use Rows(...).Delete for complete records across the worksheet; use a cell-range delete only when shifting a limited block is intentional.

The code reports a subscript error. The worksheet name may not match the tab name, or the requested sheet may be in a different workbook. Confirm the name and workbook reference. A sheet’s tab name is what a string such as Worksheets("Data") uses.

Running VBA in Excel for Microsoft 365

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and place the procedure in that module. You can run a procedure from the editor with F5, or return to Excel and choose Developer > Macros, select its name, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

Excel for Mac also supports many VBA procedures, but available features differ from Windows, and some Windows-specific automation or controls will not work. On Mac, use Tools > Macro > Visual Basic Editor to open the editor; keyboard shortcuts can vary by keyboard, so the menu route is a dependable alternative. Excel for the web does not run VBA macros. Use the desktop application for these examples.

When saving a workbook that contains macros, choose an Excel Macro-Enabled Workbook (.xlsm) rather than a macro-free .xlsx file. Only enable macros in files from sources you trust. Macro security settings may prevent a procedure from running until the file is permitted by your organization or Excel’s security controls.

Frequently Asked Questions

How do I delete one row with VBA? Use a worksheet-qualified reference such as Worksheets("Data").Rows(5).Delete to delete row 5 from Data. Qualify the workbook too if the intended sheet is not in the workbook containing the macro.

How do I delete multiple non-adjacent rows? For rows selected by a condition, loop from the bottom upward and delete each match. For a short, fixed set of row numbers, separate delete calls are straightforward. Do not assume that a comma-separated multi-area range will always behave like one contiguous row range.

Can I undo a VBA row deletion with Ctrl+Z? No. Excel’s normal undo history does not restore edits made by a macro. Save a backup before running the code, or use a separate copy for testing.

How do I delete rows that contain a word anywhere in a cell? The sample uses an exact comparison. For a contains match, test the cell text with VBA’s InStr function and still loop from the bottom upward. Decide whether capitalization should matter and handle blank or error-valued cells before converting their contents to text.

Does VBA work in Excel for the web? No. Excel for the web can preserve a workbook containing VBA, but it cannot run VBA macros. Open the file in desktop Excel for Windows or Mac to use VBA, bearing in mind that some platform-specific code may need changes.

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.