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
- Excel Sales Tracking Template with Commission: How to Build One
- Excel Sales Tracking Template by Region
- Free Excel Sales Tracking Templates: What Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Loop Through All Open Workbooks in a MacroTo loop through every workbook currently open in desktop Excel, use a For Each loop over Application.Workbooks. Declare…
- Excel VBA Offset: How to Reference Nearby CellsIn Excel VBA, Offset refers to a cell or range a chosen number of rows and columns away…
- Can VBA Create a Pivot Table? Steps and CodeYes. VBA can create an Excel PivotTable by building a PivotCache from a source range and adding a…
- VBA Range Sum: Methods, Examples, and Common ErrorsTo sum a range in Excel VBA, use Application.WorksheetFunction.Sum and assign its result to a variable or cell.…
- Excel Value Macro: Convert Formulas to ValuesTo convert selected Excel formulas into their current results with a VBA macro, assign a range’s Value2 property…
- Excel VBA AutoFilter Multiple Fields: Examples and TipsTo filter multiple Excel fields with VBA, call AutoFilter on the same range once for each column, using…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.