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.

excel clear cell contents 01

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Excel VBA clear cell contents with ClearContents Method

  1. Dion says:

    I tried the above code but received a compile error: Variable not defined and the debugger pointed to usedrng. I did modify the code a bit to fit my current need to the following:

    For Each usedrng In ActiveSheet.UsedRange
    If usedrng.Value = “” Then
    usedrng.ClearContents
    End If
    Next

    How can I fix this Compile error? Please note that I am new to VBA, but a quick learner.

    1. Wyman W Wyman W says:

      Hi Dion, usedrng is a variable being used in this Procedure. Check whether your Module contains the keyword “Option Explicit”, remove it if you see it. Alternatively, declare usedrng
      Dim usedrng as Range
      You can see my another post
      http://access-excel.tips/excel-vba-option-explicit/

Leave a Reply

Your email address will not be published. Required fields are marked *