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