VBA Excel Delete Range: Clear or Shift Cells?

To delete a range in Excel VBA and move the remaining cells up, use Range("B2:D5").Delete Shift:=xlUp; to move cells left, use Shift:=xlToLeft. If you want to remove the contents but keep the cells in place, use Range("B2:D5").ClearContents instead.

What deleting a range does

In Excel, deleting a range means removing cells from the grid, not merely erasing their values. Because the worksheet must remain rectangular, Excel shifts other cells to fill the space. A VBA Range.Delete call can specify which way those neighboring cells move, or it can delete complete rows or columns.

That distinction matters when a worksheet contains related records. Suppose columns A through D hold a person’s name, department, start date, and status. Deleting only B2:B5 and shifting cells up can move department values without moving the names, dates, or statuses beside them. The sheet may still look orderly, but the records are no longer aligned. If those entries are rows in a table, delete the entire rows or table records rather than just one column’s cells.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a mistaken deletion may require closing without saving or restoring the backup.

Basic VBA syntax and shift direction

The general pattern is someRange.Delete Shift:=direction. Use xlUp when cells below the deleted range should move upward, and xlToLeft when cells to its right should move left. Specifying the direction makes the intended result clear, especially for a rectangular range. If you omit the Shift argument, Excel determines the direction from the shape of the range; explicit code is easier to review and maintain.

This example deletes cells B2:D5 from a worksheet named Data and shifts the cells below them up:

Option Explicit

Sub DeleteRangeAndShiftUp()
    Dim ws As Worksheet

    ' Use the worksheet in the workbook that contains this macro.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Delete B2:D5 and move cells below the range upward.
    ws.Range("B2:D5").Delete Shift:=xlUp
End Sub

To shift cells left instead, change the final line to ws.Range("B2:D5").Delete Shift:=xlToLeft. The worksheet must exist with that exact tab name; otherwise, the macro stops with a subscript-out-of-range error. Qualifying the range with ws. is important: it directs the operation to the named sheet, rather than whichever sheet happens to be active.

Delete cells, clear contents, or clear everything?

Choose the operation according to what should happen to the worksheet layout. These methods are not interchangeable:

Range.Delete Shift:=xlUp or Shift:=xlToLeft removes cells and shifts neighboring cells into the space.
Range.ClearContents removes constants and formulas but leaves the cells, their formatting, and their positions in place.
Range.Clear clears cell contents and formatting, among other cell properties. Use it only when the goal is to reset the cells more completely.
Deleting a row or column removes that entire worksheet row or column, not just the selected cells.

For example, if a monthly input area should remain in the same positions for the next reporting period, clearing its contents is usually the relevant action. If a record is obsolete and the records underneath should close the gap, deleting its row is more appropriate. A common misconception is that pressing Delete on a selected cell and using VBA Delete mean the same thing: the keyboard’s Delete key normally clears cell contents, while the VBA method removes cells and can shift others.

Delete entire rows or columns

When a complete record should be removed, delete its worksheet row. This example deletes rows 3 through 5 on the Data sheet:

Option Explicit

Sub DeleteRows()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Remove complete worksheet rows 3 through 5.
    ws.Rows("3:5").Delete
End Sub

For complete columns, use ws.Columns("C:D").Delete. Deleting an entire row affects every column in those worksheet rows, including cells outside the visible data table; deleting columns similarly affects every row. Check for notes, formulas, or other information elsewhere on the sheet before using these commands. A row deletion can also change references in formulas, charts, and other dependent content as Excel adjusts them.

If the data is an Excel table, consider deleting the table’s records rather than the worksheet row when the intent is to change only that table. Worksheet-row deletion can affect neighboring content that happens to share the same rows. Table behavior also depends on the exact range and operation, so inspect the workbook after a macro that changes a table.

Delete a variable range safely

Macros often need to remove data whose final row changes from one run to the next. A common approach is to find the last non-empty cell in a chosen column, then construct the range. The following example assumes row 1 contains headings, data begins in row 2, and column A is populated for every record that should be removed:

Option Explicit

Sub DeleteDataRowsFromA2()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last populated row in column A.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Delete data rows only when at least one data row exists.
    If lastRow >= 2 Then
        ws.Range("A2:D" & lastRow).Delete Shift:=xlUp
    End If
End Sub

This removes the rectangular range from A2 through column D of the detected last row, shifting cells below that range upward within those columns. It does not delete the worksheet’s entire rows. The method relies on column A accurately marking the records: if A contains a blank in the middle, the last-row calculation still finds a later populated cell, but if the final record has a blank in A, the detected boundary may be too short. Pick a dependable key column or use a different boundary strategy when that assumption does not hold.

There is another important design choice here: deleting A:D as a block preserves alignment among those four fields, but it may not preserve alignment with related data in column E or farther right. If each record spans the whole worksheet row, delete complete rows instead. Decide what constitutes one record before deciding what range the macro should remove.

Target the right workbook and worksheet

A macro that refers to Range("B2:D5") without a worksheet qualifier acts on the active sheet. That may be the wrong sheet if a user has clicked elsewhere before running the code. Referencing ThisWorkbook.Worksheets("Data") and then using ws.Range(...) avoids that dependency. ThisWorkbook means the workbook containing the VBA code; it is not necessarily the same as ActiveWorkbook, which means the workbook currently in front.

For a macro stored in a separate personal macro workbook or add-in, this distinction can change which file is modified. If the target is the workbook currently selected by the user, state that intention in the code and verify it before deleting anything. Avoid relying on selections such as Selection.Delete in reusable procedures: the selected cells can change without the macro changing.

Sheet names must match exactly. If a sheet may be renamed, consider using its VBA code name, or add error handling that reports a missing sheet clearly. Do not silently substitute the active sheet when the expected target is missing; that can turn a helpful error into deletion from the wrong place.

Common problems and workbook effects

If a macro fails or produces an unexpected result, check these points before changing the code:

Wrong shift direction: xlUp and xlToLeft move different neighboring cells. Match the direction to the layout and inspect adjacent columns or rows.
Wrong sheet: an unqualified range acts on the active sheet. Confirm the target worksheet name and qualify the range.
Misaligned records: deleting part of a multi-column record can separate related values. Delete the whole record range or row when appropriate.
Protected sheet: worksheet protection may block deletion. Unprotect it only when authorized, and follow the workbook’s protection requirements.
Merged cells or tables: merged areas and structured table ranges can make a partial deletion invalid or produce results different from a simple grid range. Select a compatible range and test on a copy.
Unexpected formula changes: Excel may adjust references when cells, rows, or columns are deleted. Review formulas, named ranges, charts, and dependent sheets afterward.

Deleting cells does not automatically mean every related object or external reference has been handled as intended. For important workbooks, test the macro with representative data, including blank rows and edge cases, and verify both the visible output and formulas that depend on it. A range delete also should not be treated as a secure data-erasure method: workbook history, backups, or other copies may still contain prior information.

Run and maintain the macro

In desktop Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. Run it from the editor with the cursor inside the procedure, or run it from Excel using the Macros dialog. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), not as a macro-free .xlsx file, which cannot retain VBA code.

Excel for the web can open and edit many workbooks, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for this procedure. On Mac, VBA is available in desktop Excel, but editor and function-key shortcuts may differ with the keyboard and system settings; use the Developer tab’s Visual Basic command if the Windows shortcut does not apply. Only run macros from sources you trust, and follow your organization’s macro security rules.

For a destructive macro that will be used repeatedly, add clear comments, make the target sheet explicit, and consider asking for confirmation or writing a log before deletion. Confirmation is not a substitute for a backup, but it can help prevent an accidental run. Keep the deletion code narrow: a specifically named range is easier to audit than a broad command against an active selection or entire sheet.

Frequently Asked Questions

How do I delete a range in VBA without shifting cells?
Use Range("B2:D5").ClearContents to remove formulas and values while leaving cells in place. Use Clear if you also want to remove formatting and other cell properties. Neither is the same as deleting the cells.

How do I delete cells and shift left in Excel VBA?
Call Range("B2:D5").Delete Shift:=xlToLeft. Qualify the range with a worksheet variable or worksheet name so the operation cannot accidentally target a different active sheet.

How do I delete an entire row with VBA?
Use Rows("3:3").Delete to remove worksheet row 3, or use Rows("3:5").Delete for rows 3 through 5. Include the worksheet qualifier, such as ws.Rows("3:5").Delete.

Can I undo a VBA range deletion with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z will not reverse the macro’s deletion. Save a backup before running it, or close the workbook without saving if you need to discard the changes.

Why does my VBA delete macro affect the wrong worksheet?
The code may use an unqualified Range or Cells reference, which operates on the active sheet. Set a worksheet variable, such as Set ws = ThisWorkbook.Worksheets("Data"), and use ws.Range(...) or ws.Rows(...) throughout.

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.