Excel VBA: How to Delete Column and Cell Ranges Safely

To delete entire columns with Excel VBA, use a worksheet-qualified range such as ws.Columns("B:D").Delete. To delete only cells within a range, use ws.Range("B2:D10").Delete Shift:=xlShiftToLeft or xlShiftUp, depending on how the remaining cells should move.

What deleting a range means in VBA

In Excel, “delete” can mean two different things. Deleting a worksheet column removes that whole column, including its cells, formatting, and contents, and shifts columns to its right leftward. Deleting a smaller cell range removes only those cells and requires Excel to shift other cells either up or left. The choice matters because the wrong operation can change the layout of otherwise unrelated data.

Deleting is not the same as clearing. A clear operation removes contents or formatting but leaves the cells in place; deleting removes cells and changes the grid’s structure. For example, clearing B2:B10 leaves column B intact, while deleting B2:B10 and shifting up moves the cells below the deleted range upward. If you only want to remove values, use a clear method rather than Delete.

VBA ranges belong to a worksheet. Qualifying the range with a worksheet variable makes it clear which sheet the macro will change. An unqualified reference such as Columns("B:D").Delete acts on the active sheet, which may not be the sheet you intended if the user changes tabs before running the macro.

Delete one or more entire columns

Use the Columns property when the goal is to remove full worksheet columns. The following macro deletes columns B through D on a sheet named Data:

Sub DeleteColumnsBtoD()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ' Remove the full worksheet columns B, C, and D.
    ws.Columns("B:D").Delete
End Sub

The column letters identify the columns to remove; the Delete method does the structural change. You can also write ws.Range("B:D").Delete, but ws.Columns("B:D").Delete makes the intent especially clear. For a single column, use ws.Columns("B").Delete. A numeric reference such as ws.Columns(2).Delete also means column B, because column numbering starts at 1 for column A.

The example uses ThisWorkbook, meaning the workbook that contains the VBA code. That is suitable when the macro is stored in the workbook being edited. If the code is stored in another workbook, such as a personal macro workbook, set ws to the intended workbook explicitly instead of assuming ThisWorkbook is the data file.

Delete cells inside a range

When you want to remove a rectangular block without deleting the entire worksheet columns, call Delete on a cell range and specify the shift direction. This example removes B2:D10 and moves cells on the right leftward within those rows:

Sub DeleteCellsAndShiftLeft()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ' Remove B2:D10 and close the gap from the right.
    ws.Range("B2:D10").Delete Shift:=xlShiftToLeft
End Sub

To remove the same range and move cells below it upward, use Shift:=xlShiftUp instead. Choose the direction that preserves the shape and meaning of your data. Shifting left affects the specified rows; it does not delete complete columns. Shifting up affects the specified columns; it does not delete complete rows.

For instance, if each row is a complete record with a name in A, a date in B, and a status in C, deleting B2:B10 and shifting up could misalign dates and statuses with the names in column A. Deleting the whole B column may be the correct operation if the date field is no longer needed. Before deleting a partial range, check whether the nearby cells are part of the same records.

Choose the correct shift direction

Excel can sometimes infer a shift direction when the Shift argument is omitted, based on the shape of the deleted range. That inference is not a substitute for deciding what should happen to the surrounding data. Specifying xlShiftToLeft or xlShiftUp documents your intention and avoids relying on an implicit choice.

Use ws.Columns("B:D").Delete to remove full worksheet columns and shift later columns left.
Use Range("B2:D10").Delete Shift:=xlShiftToLeft when cells to the right should fill the gap in those rows.
Use Range("B2:D10").Delete Shift:=xlShiftUp when cells below should move up in those columns.
Use a clear method when cells should remain in their original positions.

In a structured dataset, deleting full rows or columns is often safer than shifting a small block, because records usually need to stay aligned across fields. A partial shift is appropriate when the worksheet layout is intentionally arranged as separate blocks rather than as one table.

Delete nonadjacent columns safely

If the columns to remove are not next to each other, deleting them one at a time from right to left avoids changing the position of a column you still plan to target. Suppose you need to delete original columns B and D. Delete D first; then B remains B. If you delete B first, original D becomes C, so a later instruction to delete D would target the wrong column.

Sub DeleteNonadjacentColumns()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ' Delete the original D before the original B shifts position.
    ws.Columns("D").Delete
    ws.Columns("B").Delete
End Sub

This right-to-left rule is useful for any list of column numbers: sort the targets from highest to lowest, then delete them in that order. For adjacent columns, a single range such as ws.Columns("B:D").Delete is simpler. Avoid selecting ranges or relying on the active cell; direct references are easier to review and are less vulnerable to a user clicking somewhere unexpected.

Excel Tables and other special cases

Excel Tables, also called ListObjects in VBA, have their own column structure. If the goal is to remove a field from a table, deleting its table column by name can express that intent more clearly than deleting a worksheet column by letter:

Sub DeleteTableColumn()
    Dim ws As Worksheet
    Dim lo As ListObject

    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("Table1")
    ' Remove the named field from the table.
    lo.ListColumns("Status").Delete
End Sub

Replace Table1 and Status with the actual table and column names. A table-column deletion changes the table structure, and formulas that use structured references may update as Excel adjusts the table. If the aim is instead to remove an entire worksheet column, use the worksheet’s Columns property and understand that other content elsewhere in that column will also be affected.

Other worksheet features can affect the outcome. A protected sheet may prevent deletion. Merged cells can make a partial-range deletion fail or behave differently than expected. Filters and hidden columns do not change the basic meaning of a column reference, so a macro can delete hidden columns too. Check the target sheet’s protection, merged cells, filters, and hidden areas before running a broad deletion macro.

Common errors and ways to avoid them

A frequent mistake is deleting from the wrong sheet because the code uses an unqualified Range or Columns reference. Another is using a cell range when the requirement is to remove whole fields, or using a full-column deletion when only a few cells should be removed. The visible gap after an operation can look similar, but the effect on surrounding data is different.

Subscript out of range: The workbook does not contain a sheet with the exact name used in Worksheets("Data"). Check spelling and spaces.
Unexpected data movement: Confirm whether the code deletes entire columns or only a cell block, and verify the shift direction.
Protected-sheet error: The sheet may need to be unprotected before deletion. Only unprotect it if you have permission and the required password.
Wrong columns removed: Earlier deletions may have shifted later targets. Delete nonadjacent columns from right to left.
Macro does not run: Save the workbook in a macro-enabled format such as .xlsm when it contains VBA, and check that macros are permitted by your organization’s security settings.

On Excel for Microsoft 365 for Windows, open the VBA editor with Alt+F11, then run a macro from Developer > Macros. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. On Mac, Excel desktop supports VBA; the editor can be opened from Tools > Macro > Visual Basic Editor, though keyboard shortcuts may vary with the keyboard and system settings. Excel for the web cannot run VBA macros, so use the desktop app for these procedures.

Safety and undo considerations

Deleting columns or cells with VBA is destructive. Unlike many manual worksheet edits, a macro action cannot be reversed with Ctrl+Z. Save a copy of the workbook before running any deletion macro, especially if it processes multiple sheets or uses calculated column positions. Consider first testing the code on a duplicate workbook with representative data.

For a repeatable process, make the target explicit, use a named worksheet, and add a confirmation step when a person is running a high-impact macro. A confirmation prompt is not a substitute for a backup, but it can catch an accidental run. If a macro is intended to run unattended, document the exact columns it removes and keep an untouched source file or backup as part of the workflow.

Frequently Asked Questions

How do I delete a column in Excel VBA? Set a worksheet variable, then use a qualified reference such as ws.Columns("B").Delete. This removes the entire B column on that worksheet, not just the cells containing visible data.

How do I delete a range and shift cells left? Use ws.Range("B2:D10").Delete Shift:=xlShiftToLeft. This removes that cell block and shifts cells from the right into the gap in the affected rows.

How do I delete a range and shift cells up? Use ws.Range("B2:D10").Delete Shift:=xlShiftUp. The cells below the deleted block move up within the affected columns. Check that this will not break row alignment with related data.

Does VBA Delete clear contents or remove cells? Delete removes cells or entire columns and causes surrounding cells to shift. To keep the grid structure unchanged while removing values, use a clear operation instead.

Can I undo a VBA column deletion with Ctrl+Z? No. Excel’s normal undo history does not reverse VBA macro actions. Save a copy of the workbook before running the macro so you can restore the original if needed.

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.