VBA .Clear Versus .ClearContents

In VBA, there are multiple methods you could use to clear the values of a cell on a spreadsheet. Here we quickly discuss two methods and highlight the difference between the two methods.

Method 1 – Range.Clear

Clear is used when you wish to clear the formulas and/or values from a range of cells, as well as all formatting applied to the cells. In the following example, the formulas and values in the range A1:A10 would be cleared, as well as any formatting applied to the cells.

Sheet1.Range("A1:A10").Clear

This is similar to selecting Clear All on the Home tab of the Excel Spreadsheet

Method 2 – Range.ClearContents

ClearContents is used when you wish to clear the formulas and/or values from a range of cells, but you wish to leave the formatting of the cells alone. In the following example, the formulas and values in the range A1:A10 will be cleared, but the formatting of those cells will remain intact.

Sheet1.Range("A1:A10").ClearContents

This is similar to selecting Clear Contents on the Home tab of the Excel Spreadsheet

Other Methods

There are other methods to clear the contents of a range of cells. Two other methods would be to set the value to an empty string or to a vbNullString. Both of these methods should preserve the formatting of the cells and only change the value of the cell. Examples of these methods are provided below.

Sheet1.Range("A1:A10").Value = ""

Or

Sheet1.Range("A1"A10").Value = vbNullString

For most users, there is not much difference between these two options, however, they are different. Using the double quotes is slightly more resource intensive. This is because "" is an empty string. While the string is empty, it is still a string. Using vbNullString allows you to essentially erase the value of the cell completely. It is equivalent to using NULL in other programming languages. If total and complete optimization is your goal, then using vbNullString is preferable to ""