Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac
- 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.
In VBA, there is a method called ClearContents that you can use to clear values and formulas from a cell, range of cells, and the entire worksheet.
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 if you want to clear values from the cell A1. First, you need to define the 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 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:
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
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
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel
If you are preparing for an interview, make sure to check out these VBA interview questions.