Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac
Key Points
- To clear content from a cell, you need to use the ClearContents method.
- You can use it for a single cell, a range, or an entire worksheet.
ClearContents Method
In VBA, there is a method called ClearContents that you can use to clear values and formulas from a cell, a range of cells, and the entire worksheet.
Expression.ClearContents
To use this method, first, you need to define the expression somewhere you want to clear the content, and then type “.ClearContents”.
Clear Contents from a Single Cell
Let’s say you want to clear values from cell A1. First, you need to define cell A1 using the range object and then the ClearContents method.
So the code would be:
Sub ClearContentExamples()
Range("A1").ClearContents
End Sub
You can also use the cells property to define a cell and the code would be:
Sub ClearContentExamples()
Cells(1, 1).ClearContents
End Sub
Clear Contents from a Range
In the same way, you can define a range and then use the ClearContent method to clear values and formulas from that range. Let’s say you want to clear values from the range A1:A10, in this case, the code would be something like the below.
Sub ClearContentExamples()
Range("A1:A10").ClearContents
End Sub
Clear Content from an Entire Worksheet
To clear content from an entire worksheet, you can use the below code:
Sub ClearContentExamples()
Cells.ClearContents
End Sub
And to clear from a specific worksheet:
Worksheets("Sheet1").Cells.ClearContents
Clear Content from the Selection
If you want to write a dynamic code that can clear contents from the selected cells, then you can use the below code.
Sub ClearContentExamples()
Selection.ClearContents
End Sub
Other Methods
Below are some of the other methods that you can use:
Sub ClearContentExamples()
Range(“A1”).Clear ‘Clears Everything
Range(“A1”).ClearComments ‘Clear Comments
Range(“A1”).ClearFormats ‘Clears Formatting
Range(“A1”).ClearHyperlinks ‘Clear Hyperlinks
Range(“A1”).ClearNotes ‘Clear Notes
Range(“A1”).ClearOutline ‘Clears Outline
End Sub
Related Tutorials
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells