Excel VBA: Clear Entire Column Contents

VBA & macros · Updated November 22, 2026

To clear the contents of an entire column in Excel VBA, use the ClearContents method on the column’s Range object, for example: Columns("B").ClearContents. This removes all cell values from the column while preserving formatting, comments, and data validation.

Why ClearContents Instead of Delete or Clear?

Excel offers several ways to remove data, and the right choice depends on what you want to keep. ClearContents only wipes the values (and formulas) in the cells, leaving formatting, column width, comments, and data validation intact. This is ideal when you want to reuse the column for new data without losing its appearance or rules.

In contrast, Range.Clear removes everything—values, formatting, comments, and validation—resetting the cells to a blank slate. Range.Delete shifts the remaining cells up or left, which can break formulas that reference the deleted cells and alter the worksheet layout. For most “clear the data” tasks, ClearContents is the safest and most predictable method.

Basic Syntax and Examples

The core syntax is straightforward:

Columns("C").ClearContents

This clears column C (the third column) from row 1 to the last used row in that column. If you want to clear a specific range within a column, you can specify it:

Range("A1:A100").ClearContents

But to clear an entire column regardless of how many rows contain data, Columns("D") is the most reliable. You can also refer to columns by number:

Columns(5).ClearContents  ' Clears column E

If you need to clear multiple columns at once, use a union:

Union(Columns("B"), Columns("D")).ClearContents

Using Variables and Dynamic Columns

Often you’ll want to clear a column based on a variable, such as a column header or a user-selected cell. Here’s an example that finds a column by its header name and clears it:

Dim targetCol As Long
targetCol = Application.Match("Sales", Rows(1), 0)  ' Find column with "Sales" in row 1
If Not IsError(targetCol) Then
    Columns(targetCol).ClearContents
Else
    MsgBox "Header not found"
End If

This uses Application.Match to locate the column number, then clears it. Always check for errors when using Match, as it returns an error if the value isn’t found.

Clearing Only Used Cells vs. Entire Column

Some users worry that Columns("A").ClearContents will clear millions of rows, slowing down the workbook. In practice, Excel only clears cells that are in the used range of that column, but the used range can extend far beyond your data if you’ve formatted or edited cells in the past. To avoid clearing unintended rows, you can limit the range to the used rows:

Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastRow).ClearContents

This finds the last row with data in column A and clears only up to that point. This is especially useful when your worksheet has stray formatting in distant rows.

Common Problems and How to Avoid Them

One frequent issue is that ClearContents does not remove data validation dropdowns or conditional formatting. If you need to remove those as well, you must clear them separately:

With Columns("B")
    .ClearContents
    .Validation.Delete
    .FormatConditions.Delete
End With

Another problem is that clearing a column can break formulas that reference cells in that column. For example, if cell F1 contains =SUM(A1:A10) and you clear column A, the formula returns 0. This is expected behavior, but you may want to alert the user or recalculate after clearing.

Also, be aware that ClearContents does not trigger worksheet events like Worksheet_Change unless you use Application.EnableEvents = False temporarily. If you have event handlers that should run when data changes, you’ll need to handle that manually.

Performance and Speed Considerations

Clearing a large column can be slow if Excel recalculates after each cell. To speed up the process, disable screen updating and automatic calculations before clearing:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Columns("C").ClearContents
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Remember to reset these settings even if an error occurs—use On Error Resume Next or a Finally-like structure to ensure they’re restored.

Alternatives to VBA

If you don’t need VBA, you can clear a column manually: select the column by clicking its letter, then press Delete on your keyboard. This is equivalent to ClearContents—it removes values but keeps formatting. On a Mac, the same key works. For a more thorough reset, use Home > Clear > Clear All (which is the same as Range.Clear).

For repetitive tasks, you can also use Excel’s built-in “Go To Special” feature to select blank cells or constants, but VBA gives you the most control and automation.

Frequently Asked Questions

Does ClearContents remove formulas?

Yes, it removes both values and formulas. If you want to keep formulas but clear their results, you’d need to change the calculation mode or use a different approach, but typically you clear formulas too.

How do I clear a column but keep the header?

You can clear from row 2 down: Range("A2:A" & Rows.Count).ClearContents. Or if you want to clear the entire column including header, just use Columns("A").ClearContents.

Why doesn’t ClearContents work on a filtered list?

When a worksheet is filtered, ClearContents on the entire column clears all rows, including hidden ones. To clear only visible cells, use Range.SpecialCells(xlCellTypeVisible). For example: Columns("A").SpecialCells(xlCellTypeVisible).ClearContents.

Can I undo a VBA clear?

No, macros cannot be undone with Ctrl+Z. Always save a copy of your workbook before running a macro that clears data. You can also add a confirmation prompt in your code.

What’s the difference between ClearContents and Clear on a column?

Clear removes everything—values, formatting, comments, and validation—while ClearContents only removes values and formulas. Use Clear when you want a truly blank column.

Related

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.