This Excel tutorial explains how to clear cell contents / clear cell formats using Clear, ClearContents, ClearFormats Method.
Excel VBA clear cell contents
There are several kinds of “clear” related Excel VBA Methods for different scenarios. I will also explain how to clear single cell contents, clear merged cells contents, and clear blank cells.
Clear single cell contents with ClearContents
There are two ways to clear single cell contents (clear value in a Cell but not formatting)
Assume that you want to clear contents in cell A1, then do one of the followings
Method 1: Range("A1").value = ""
Method 2: Range("A1").ClearContents
No matter which method is used, the result will return TRUE if you use IsEmpty() Function on the cell.
Clear merged cell contents
For merged cells, you may not use ClearContents directly on the Cell object as you do on a single cell, otherwise you will receive an error message.
Run -time error ‘1004’: We can’t do that to a merged cell
To clear merged cell, first you need to understand how merged cells behave.
Merged cell such as A1:C1, the value always lies in the cell on the top left (A1). If you run a code such as B1.value = “something”, nothing will change in the spreedsheet, you will not find the value even if you use msgbox to show B1.value. The value in the merged cell is changed only if you type A1.value = “something”.
To clear merged cell data, there are two ways to do that. Take A1:C1 as an example.
Change the top left cell (A1) value. Note that changing the value of B1 and C1 will not affect the merged cell value.
A1.value = ""
Set a Range (any single range within the merged cell: A1/B1/C1) as variable and then use ClearContents Method.
Set Rng = Range("A1") Rng.MergeArea.ClearContents
Clear empty cell
Sometimes you may find that a cell looks empty, but when you use COUNTA function on that cell, you count something, meaning that the cell is technically not empty. One major reason is that the data source was downloaded from other systems and export as Excel.
In order to make those fake empty cells truly empty, you can use the below procedure, which handles both single cells and merged cells.
Public Sub clear_empty_cell() For Each usedrng In ActiveSheet.UsedRange If usedrng.MergeCells = True Then If usedrng.Value = "" Then usedrng.Value = "" End If Else If usedrng.Value = "" Then usedrng.ClearContents End If End If Next End Sub
After “data cleansing”, you may want to remove blank rows from worksheet, click here to read more.
Other clear Methods
There are several more clear Methods which are self explanatory.
|Remove formatting and contents
|Remove cell formatting, including font format
|Reomve hyperlinks, but the default format of hyperlink is not removed