How to clear contents of a cell or range in Excel VBA?

In this article, you will learn VBA to clear contents of a cell, clear contents and formatting VBA, clear number cells only, clear text cells only and clear cell content if the cell having a specific text.

VBA to clear contents of range

Below VBA code snippets will only clear the contents of range or cells.

1st code line will clear the contents of an active sheet range

2nd code line will clear the contents of a specified sheet, in this example Sheet2

3rd code line will clear contents of a non-contiguous range

Sub clearConentOnly()
 'clear content of Activesheet range
 ActiveSheet.Range("A2:A6").ClearContents
 'clear content of Sheet2 (code name)
 Sheet2.Range("A2:A6").ClearContents
 'clear content non contiguous range
 Sheet2.Range("A2:A6,C2:C6").ClearContents
End Sub

Excel VBA to clear contents and formatting

The below VBA clears contents and formatting of a range.

  Sheet2.Range("A2:A6,C2:C6").ClearContents
  Sheet2.Range("A2:A6,C2:C6").ClearFormats

Tip:- Instead of referring Sheet2.Range(“A2:A6,C2:C6”) again and again you can write shorthand code of the above lines using With statement.

  With Sheet2.Range("A2:A6,C2:C6")
   .ClearContents
   .ClearFormats
  End With

Note that dot (period) is there before ClearContents and ClearFormats, which indicates methods performed on the same range written right side of With.

You can also use the Clear method of a range, but this will delete all things including cell comments also.

  'Clear will delete everything of a range
  Sheet2.Range("A2:A6,C2:C6").Clear

To clear all cells of a worksheet you can use the below code (VBA clear contents of sheet)

Sheet2.Cells.Clear

VBA to clear contents of a specific range in all the sheets

The below code section clears contents of Range(“A2:A6,C2:C6”) in all the sheets of a workbook.

Sub clearAllSheets_rangeContent()
 Dim sht As Worksheet 
 For Each sht In ThisWorkbook.Worksheets
   sht.Range("A2:A6,C2:C6").ClearContents
 Next sht
End Sub

Other VBA methods for clearing a range

If you write range object and put dot then you can see the IntelliSense list.

When the IntelliSense list appears write letters of a keyword (here clea) which you want to write.

You can see IntelliSense is showing seven methods for clearing specific things in a range.

Use can use CTRL+SPACE to bring IntelliSense list if not coming automatically.

Use specific methods to suit your requirement.

Example:- If you want to delete comment in a range or cell then use ClearComments. To delete hyperlinks use ClearHyperlinks.

Delete specific data type present in range

You can delete cells with specific data types like clearing cells having numbers, clearing cells with text, or clearing formula cells present in a range.

For doing this SpecialCells method of the range object is used.

Clear cells with numbers

The below VBA code clears cells with numbers in a Sheet2 range.

Sub clearNumbersOnly()
  Sheet2.Range("A2:A6,C2:C6") _
  .SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
End Sub

Clear cells with text

The below VBA code clears cells with text in a range of Sheet2.

Sub clearTextOnly()
  Sheet2.Range("A2:A6,C2:C6") _
  .SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
End Sub

Clear content of a range if cell having a specific text

In the below code section Sheet5 is the code name of a worksheet. 

Range E2:E5 represents a list of values to be cleared from cells present in range A1:C6.

Sub clearCellText_FromList()
 Dim listRng As Range
 Dim findInRng As Range
 Dim cel As Range
 Set listRng = Sheet5.Range("E2:E5")
 Set findInRng = Sheet5.Range("A1:C6")
 For Each cel In listRng
    findInRng.Replace what:=cel.Value, _
    replacement:=vbNullString, lookat:=xlWhole
 Next cel
End Sub