VBA Delete Rows in a Range: Safe Methods and Examples

To delete entire worksheet rows that intersect a range, use the range’s EntireRow property and call Delete, for example ws.Range("B5:B12").EntireRow.Delete. To remove cells only from the selected range instead, use Delete Shift:=xlUp; these two operations have different effects, so choose deliberately and save a copy before running a macro.

What “delete rows in a range” means

In Excel VBA, “delete rows in a range” can mean either removing whole worksheet rows or removing cells from a limited area and shifting other cells to fill the gap. The distinction matters because a worksheet row can contain related data across many columns. Removing only part of that row may leave values that used to belong together on different lines.

A VBA Range can refer to one cell, several cells, or a rectangular block. The EntireRow property expands the reference to all worksheet columns for each row it touches. The Delete method then removes those rows and shifts the rows below them upward. For example, a reference to B5:B12 identifies rows 5 through 12; it does not limit the deletion to column B when you use EntireRow.Delete.

If the goal is to remove records from a table, deleting entire rows is usually the intended action. If the goal is to remove a block of cells while preserving data elsewhere on the same worksheet rows, delete only the range and specify how the remaining cells should shift.

Delete entire worksheet rows

Here is a complete macro for deleting worksheet rows 5 through 12 on a worksheet named Data. The reference is explicitly qualified with a worksheet variable, so the code does not rely on whichever sheet happens to be active.

Sub DeleteRowsInRange()
    Dim ws As Worksheet
    Dim rowsToDelete As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set rowsToDelete = ws.Range("B5:B12")

    ' Delete the complete worksheet rows touched by the range.
    rowsToDelete.EntireRow.Delete
End Sub

The column used to identify the row range is not special: ws.Range("A5:A12").EntireRow.Delete or ws.Range("B5:D12").EntireRow.Delete also removes rows 5 through 12. A multi-column reference spanning rows 5 to 12 still results in those complete rows being deleted. Use a range that starts and ends on the rows you actually intend to remove.

For a worksheet table, deleting its worksheet rows can affect other content located on those rows, including information outside the table. If you only want to remove table records, consider using the table’s own data-row methods or carefully target its data body rather than assuming the whole worksheet row is isolated.

Delete cells only within a range

To delete cells in a limited area, call Delete on that area and specify the shift direction. For example, this removes four cells from column B and shifts the cells below them up within that column:

ws.Range("B2:B5").Delete Shift:=xlUp

For a rectangular range such as B2:D5, the cells in those columns shift upward. Data in columns A and E is not shifted with them. That can break the relationship between fields in a record—for instance, a customer name may remain on one row while the associated order number moves to another. Use this method only when independent column movement is intended.

You can also use Shift:=xlToLeft when the desired result is to shift cells to the left. For a whole-row deletion, do not rely on either shift option: use EntireRow.Delete to make the scope unambiguous. Similarly, EntireColumn.Delete removes complete worksheet columns, not just cells in the original range.

Delete rows that meet a condition

When deleting rows based on their contents, loop from the bottom of the data toward the top. Deleting a row moves the rows below it, so a top-to-bottom loop can skip the next row after a deletion. This example deletes blank entries in column B from rows 2 through 100, preserves row 1 as a header, and ignores cells containing Excel errors.

Sub DeleteBlankRowsInColumnB()
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim cellValue As Variant

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Work upward so deletions do not skip remaining rows.
    For rowNum = 100 To 2 Step -1
        cellValue = ws.Cells(rowNum, "B").Value
        If Not IsError(cellValue) Then
            If Len(Trim$(CStr(cellValue))) = 0 Then
                ws.Rows(rowNum).Delete
            End If
        End If
    Next rowNum
End Sub

This treats genuinely empty cells, formulas returning an empty string, and cells containing only ordinary leading or trailing spaces as blank. It does not treat a cell containing zero as blank. If your definition of blank is different—for example, a cell containing a space should count as populated—adjust the test to match the data rules.

The example uses a fixed last row so its boundaries are obvious. You can calculate a last row with ws.Cells(ws.Rows.Count, "B").End(xlUp).Row, but that finds the last nonempty cell in column B. If records continue below the last entry in B, or if B is not a reliable column for locating records, that calculation may miss rows. Choose a column that is consistently populated or determine the data extent another way.

Choose between a loop and filtering

A bottom-up loop is straightforward when the condition is simple and the dataset is moderate in size. It also makes it easy to preserve a header, check for errors, or apply several tests. Its main drawback is that deleting rows one at a time can take longer on a very large sheet, particularly if calculation or event procedures run after each change.

For a large, regularly structured list, AutoFilter can be a practical alternative: filter the data to show the records that should be removed, then delete the visible data rows while keeping the header. Be precise about the range passed to the filter and the rows selected for deletion. A broad selection can remove the header or unrelated visible cells, and filtered lists can contain noncontiguous visible areas. Test the selection before deleting; code using SpecialCells(xlCellTypeVisible) also needs to account for cases where no matching visible data cells exist.

Use EntireRow.Delete when each matching record occupies a whole worksheet row.
Use Range.Delete Shift:=xlUp when only cells in the specified columns should move.
Use a bottom-up loop for clear, row-by-row conditions or when custom checks are needed.
Consider filtering for large lists, but explicitly exclude headers and verify the visible selection.

Common mistakes and safeguards

One frequent problem is an unqualified reference such as Range("B5:B12").EntireRow.Delete. Without a worksheet qualifier, VBA uses the active sheet. If another sheet is active when the macro runs, it may delete rows there instead. Assign the intended worksheet to a variable and use ws.Range or ws.Rows consistently.

Another mistake is assuming that a cell range describes the full deletion scope. Range("B5:B12").Delete and Range("B5:B12").EntireRow.Delete are not interchangeable. The first deletes cells in the range and needs a shift direction; the second removes whole worksheet rows. Confirm the intended outcome with a small, disposable example before applying the macro to important data.

Also check the boundaries. A range beginning at row 1 can remove a heading, while an incorrect end row can include totals or notes. If the worksheet is protected, deletion may be blocked unless the sheet is unprotected or configured appropriately. If formulas, tables, merged cells, or event macros are involved, inspect the resulting workbook rather than assuming every related feature will behave as intended.

Most importantly, save a separate copy of the workbook before running any deletion macro. Excel’s Undo command, including Ctrl+Z on Windows, cannot undo changes made by a VBA macro. A backup is the reliable way to recover the original rows if the range or condition was wrong.

Run VBA in Excel for Windows or Mac

In desktop Excel, open the Visual Basic Editor from the Developer tab’s Visual Basic command, insert the macro into a standard module, and run it from the editor or Excel’s macro interface. On Windows, Alt+F11 opens or switches to the Visual Basic Editor. On Mac, Option+F11 is commonly used; depending on the keyboard, you may also need the Fn key. The Developer tab and keyboard settings can vary, but the basic range-deletion statements work in desktop Excel for both platforms.

Excel for the web does not run VBA macros. Open the workbook in the desktop app to run this code. If you need to preserve a macro in the workbook, save it in a macro-enabled format such as .xlsm; an ordinary .xlsx file does not store VBA code. Only enable or run macros in workbooks you trust, and keep a backup before deletion.

Frequently Asked Questions

How do I delete a specific row with VBA?
Use a worksheet-qualified row reference, such as ws.Rows(5).Delete, to delete worksheet row 5. The cells below move up to fill the gap.

How do I delete multiple non-adjacent rows?
You can delete them individually in descending row-number order, or build a carefully checked union of row ranges and delete it. Descending order matters when deleting one row at a time because each deletion changes the row numbers beneath it.

Does deleting a range with VBA remove the whole row?
Not by itself. Range.Delete removes the selected cells and shifts cells according to the specified direction. Add EntireRow before Delete when you mean to remove complete worksheet rows.

Can Ctrl+Z undo a VBA row deletion?
No. Excel clears its normal undo history when VBA changes the workbook, so Ctrl+Z will not restore the deleted rows. Save a copy first, or close without saving if you need to discard the macro’s changes.

Can I run a VBA deletion macro in Excel for the web?
No. Excel for the web does not run VBA. Use desktop Excel for Windows or Mac to execute the macro; a workbook can still be stored or viewed online, but the VBA action requires the desktop app.

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.