VBA ClearContents (from a Cell, Range, or Entire Worksheet)

Last Updated: August 07, 2023
puneet-gogia-excel-champs

- Written by Puneet

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
clear-contents-from-a-single-cell

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-single-cell-example

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-contents-from-range

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
clear-contents-from-an-entire-sheet

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.

clear-content-from-the-selection
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