VBA Range.Delete: Remove Cells, Rows, or Columns

In Excel VBA, use the Delete method to remove cells, rows, or columns: for example, Range("B2:C5").Delete Shift:=xlShiftUp deletes those cells and shifts the cells below them upward. To remove entire rows or columns instead, use EntireRow.Delete or EntireColumn.Delete; to keep the cells but remove their contents, use ClearContents.

The important choice is what should fill the space left behind. Deleting a cell range changes the worksheet structure by shifting other cells, while deleting whole rows or columns removes those worksheet rows or columns. A carefully qualified range and an explicit shift direction make the result more predictable.

What Range.Delete does

Range.Delete removes the specified cells from a worksheet. If the range is smaller than a full row or column, Excel needs to close the gap. It can shift surrounding cells up or to the left. If the range is a complete row or column, the corresponding worksheet row or column is removed.

For example, deleting B2:B4 with an upward shift removes those three cells and moves the cells below them up. It does not remove the entire worksheet rows 2 through 4: values in columns A and C and beyond remain in their original rows. By contrast, deleting rows 2 through 4 removes every cell in those worksheet rows and moves the rows below upward.

This distinction matters in structured data. If each record uses several columns, deleting only one column’s cells can misalign that column with the rest of its records. When a record should disappear as a unit, delete its entire row or the corresponding table row.

Syntax and shift direction

The method accepts an optional shift direction. For a partial-cell range, specify the direction explicitly rather than relying on Excel to infer it from the shape of the range.

Range("B2:C5").Delete Shift:=xlShiftUp
Range("B2:C5").Delete Shift:=xlShiftToLeft

xlShiftUp moves cells below the deleted range upward; xlShiftToLeft moves cells to its right leftward. These are Excel’s built-in VBA constants. The second line, for instance, removes B2:C5 and brings the cells that were in columns D and E into columns B and C for the affected rows.

If you omit Shift, Excel chooses a direction based on the range. That can be convenient for a quick operation, but it makes code less clear and can produce an unintended layout when the selected range changes. Use an explicit direction for partial ranges. For an entire row or column, use the corresponding whole-row or whole-column method instead of specifying a shift.

Delete cells with a specific shift

Qualify the range with a worksheet so the code does not depend on whichever sheet happens to be active. In this example, the range B2:C5 on the sheet named Data is removed, and the cells below it shift up:

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.

Sub DeleteCellsAndShiftUp()
    Dim ws As Worksheet

    ' Work on the named sheet, not the active sheet.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("B2:C5").Delete Shift:=xlShiftUp
End Sub

For a leftward shift, replace xlShiftUp with xlShiftToLeft. Choose the direction based on how the worksheet is organized: shifting upward usually makes sense when removing entries from a vertical list, while shifting left can make sense when removing cells from a horizontal layout. The choice affects only the cells in the deleted range’s rows or columns, not necessarily an entire table of related data.

Delete complete rows or columns

Use EntireRow.Delete when all data in the affected worksheet rows belongs to the record being removed. Use EntireColumn.Delete when you want to remove the whole worksheet columns.

Sub DeleteRowsAndColumns()
    Dim ws As Worksheet

    ' Save a workbook copy before running this macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Rows("3:5").Delete
    ws.Columns("D:D").Delete
End Sub

The row instruction removes rows 3 through 5 across the worksheet, not just cells in the Data table or a selected block. The column instruction removes column D. Because the second operation runs after the first, the column deletion still targets D, but any later code referring to row or column positions should account for the changed layout.

For a range object, the equivalent style is ws.Range("B2:C5").EntireRow.Delete or ws.Range("B2:C5").EntireColumn.Delete. These remove every row or column touched by the range. Thus, the first expression deletes worksheet rows 2 through 5 even though the original range covers only columns B and C.

Delete versus ClearContents

Deleting and clearing are not interchangeable. Delete removes cells and may shift other cells or remove entire rows or columns. ClearContents leaves cells in place but removes their values and formulas. If the goal is to empty B2:C5 without shifting anything, use:

Sub ClearCellContents()
    Dim ws As Worksheet

    ' Clear values and formulas without shifting cells.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("B2:C5").ClearContents
End Sub

Clearing contents generally leaves formatting and data validation in place. The separate ClearFormats method clears formatting, while Clear clears the cell contents and formatting along with other cell properties. Choose the operation that matches the intended result; deleting cells merely to blank them can shift neighboring data and damage the layout.

For instance, if a worksheet has a fixed template with labels and formulas arranged in specific positions, clearing a user-entered value is usually safer than deleting its cell. If a row represents a record that should no longer exist, deleting the row may be appropriate.

Deleting rows while checking conditions

A common task is to remove every record that meets a condition. Deleting rows from top to bottom can skip records: after a row is deleted, the next row moves into its place, and a simple loop may advance past it. Iterate from the bottom upward so that deleting a row does not change the positions of rows that still need checking.

Save a copy of the workbook before running this example; the macro’s deletions cannot be reversed with Ctrl+Z.

Sub DeleteBlankEntries()
    Dim ws As Worksheet
    Dim rowNum As Long

    ' Check column A from the last used row upward.
    Set ws = ThisWorkbook.Worksheets("Data")
    For rowNum = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Len(ws.Cells(rowNum, "A").Value) = 0 Then
            ws.Rows(rowNum).Delete
        End If
    Next rowNum
End Sub

This checks column A from row 2 down to the last nonempty cell in that column, preserving row 1 as a header. It removes rows where the checked cell is empty. If the data has meaningful records below the last used cell in column A, or if a formula returns an empty string, adjust the last-row and condition logic to match the worksheet. Always test a deletion macro on a copy or a small sample first.

Tables, merged cells, and other complications

Excel tables, also called ListObjects, have their own row structure. If the intent is to remove one data record from a table, deleting the table’s data row is clearer than deleting an arbitrary cell range within it. For example, with a table named SalesTable, its third data row can be removed with ThisWorkbook.Worksheets("Data").ListObjects("SalesTable").ListRows(3).Delete. The index here refers to the table’s data rows, not worksheet row numbers. Confirm that the table name and index exist before running such code.

Merged cells can also complicate deletion. A range that intersects a merged area may not be deletable in the way a regular rectangular range is, and Excel can return an error when the operation conflicts with the merged layout. Unmerge or redesign the affected area before automating deletions if possible.

Noncontiguous selections are another reason to be cautious. A range made from separate areas does not always behave like one simple rectangle when deleted, and some operations on multiple selections are rejected. If deleting several rows based on a condition, a bottom-up loop over row numbers is often easier to reason about than a multi-area range.

Common errors and safer practices

Wrong sheet: An unqualified Range("B2:C5") refers to the active sheet. Use ws.Range(...) after setting ws to the intended worksheet.
Wrong scope: Range("B2:C5").Delete deletes cells, not necessarily complete records. Use EntireRow.Delete when whole worksheet rows must go.
Unexpected shift: Set Shift:=xlShiftUp or Shift:=xlShiftToLeft for a partial-cell deletion.
Protected sheet: Excel may reject deletion when the worksheet is protected. Unprotect it through an authorized workflow before the macro runs, or design the workbook’s protection settings to allow the intended action.
Moving references: Deletions alter row and column positions. Later code that uses fixed addresses may refer to different records after a deletion, so identify data by a stable key when appropriate.
No convenient undo: A VBA deletion cannot be reversed with Ctrl+Z. Keep a backup, test on a copy, and consider asking for confirmation before a large or irreversible operation.

For a manual deletion in Excel for Windows, select the cells and press Ctrl+- to open the Delete dialog, then choose whether to shift cells or remove whole rows or columns. The Home > Delete menu also provides deletion choices. Shortcut behavior can vary with keyboard and Excel for Mac settings; on Mac, use the Home ribbon’s Delete controls if the minus shortcut does not invoke the expected dialog. VBA code itself is the same for the standard Excel object model on Windows and Mac, although platform-specific workbook features may differ.

Frequently Asked Questions

How do I delete a range in VBA without shifting cells?
Use ClearContents if you want to empty the cells and keep their positions. The Delete method removes cells; for a partial range, Excel must shift other cells to close the gap.

What is the difference between Range.Delete and EntireRow.Delete?
Range("B2:C5").Delete removes cells in B2:C5 and shifts cells in a direction. Range("B2:C5").EntireRow.Delete removes worksheet rows 2 through 5 across all columns.

How do I delete a column in VBA?
Use a worksheet-qualified expression such as ws.Columns("D:D").Delete to remove all of column D. To remove only cells in part of that column, use Delete Shift:=xlShiftUp on the specific range.

Why does VBA skip rows when deleting in a loop?
When a row is deleted, the following row moves up into its position. A loop moving downward can then skip that moved row. Count downward from the last row using Step -1 when testing and deleting rows.

Can I undo a VBA range deletion with Ctrl+Z?
No. Running a macro clears Excel’s normal undo history, so a deletion performed by VBA cannot be restored with Ctrl+Z. Save a separate copy or build a deliberate backup-and-restore process before running deletion code.

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.