Excel VBA clear cell contents with ClearContents Method

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.

Method 1

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 = ""

Method 2

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.

Clear	Remove formatting and contents
ClearFormats	Remove cell formatting, including font format
ClearComments	Remove comments
ClearHyperlinks	Reomve hyperlinks, but the default format of hyperlink is not removed
Clear Notes	Remove Notes
ClearOutline	Remove Outline

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff835589.aspx