The longer you’ve been programming, the more tasks you’ll automate. You’ll likely encounter proccesses you want to automate that have slight differences, and to simplify both as much as possible, you’ll build two separate functions or subs.

For example, you might notice that Process A is better when Variable X is required, while Process B is better when Variable X is optional. In this scenario, you end up with Sub A and Sub B, which you can access independently.

This is often how similar built-in functions arise. In our case, we’ll look at the difference between VBA ClearContents and the VBA Clear methods as part of a Range in Excel. Their names imply similarity, and they do both clear information from cells. But important differences exist.


VBA Clear Method

The VBA Clear method is associated with a Range object in Excel. A Range is just a group of cells. Let’s say you have a table of information for a community center that looks like this:

Sample Excel Table

Here, we have formulas in the Time Out column and the More Members cell, plus conditional formatting for the Member, Return Time, and Time Out columns. Moreover, we’ve used date formatting for the Date column, which can automatically infer the day of the week for us. We might even consider adding data validation for the Check Out Time so users can’t enter invalid times, like before the community center opens.

Perhaps you trust your visitors to fill in the correct information, and you simply look at the table every weekend. Or if you are very ambitious, maybe you set up a spiffy customer-facing page complete with appealing graphics and receive data in a userform, then look at the results once a week.

With a few visitors it would be trivial to select the table with your mouse and reset it. But what if you have hundreds of visitors? You probably won’t want to scroll down to find the bottom then highlight the whole table and delete it. Moreover, if you just select and press Del, you’ll lose formulas. In our case, you’d need at least two selections to avoid removing the Time Out column.

For that reason, you might want to simply implement a button to clear everything with this code:

Sub clear_customer_table()
Range(Cells(4, 1), Cells(10000, 4)).Clear
Range(Cells(4, 6), Cells(10000, 7)).Clear
End Sub

This will preserve your formulas in the Time Out column (the 5th column).

Unfortunately, the .Clear method has removed all of our formatting in the cleared ranges:

  • Return times past 5:30pm are no longer conditionally highlighted, nor are members conditionally marked in green.
  • Our date formatting is gone. If someone enters a date in the usual format, such as 09/15/2019, they won’t see the commas or day of the week.
  • The cell borders and fill colors have vanished.
  • All text and cell formatting preferences, like bold, font size, and shading are cleared.
  • If we had any data validation rules, such as no Check Out Times before 9am, these too would be removed.

The VBA Clear method resets your cells to the “fundamental form” you see when you first open a new workbook. The only exception is cell sizing, which remains. If we just want to remove contents but retain formatting, we can use Clear’s close cousin, ClearContents.


VBA ClearContents Method

After repopulating our table to the original we had above, let’s try to clear the data but retain the formatting we worked so hard to create. This can be accomplished with the VBA ClearContents method. Instead of clearing everything, ClearContents just clears the data in the cells. The numbers, text, dates, and times will be removed, but all formatting is retained. “Contents” simply refers to the information users usually enter in a cell.

Sub clear_customer_table_contents()
Range(Cells(4, 1), Cells(Rows.Count, 4)).ClearContents
Range(Cells(4, 6), Cells(Rows.Count, 7)).ClearContents
End Sub

This second code block is much nicer. We don’t have to worry about setting up cell, text, date, number, or border formatting again, and we retain all our rules for conditional formatting and data validation.

You could assign this macro to a button or a shape directly on your Excel sheet for easy clearing. But can we make the code any prettier? Answer: yes.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I developed this unique 3-part free Excel training program to help you quickly learn VBA in a natural setting: right inside Excel. I'm also going to give you my entire personal macro library for free.

Let me join the free wellsrPRO VBA Training program

Clear Contents while Retaining Formulas

While the person clearing the table may only need one step (pressing the “Clear” button you add to the sheet), the code is still two steps: clear contents to the left of the Time Out column and clear contents to the right of the Time Out column. By default, the VBA ClearContents method would remove formulas entered in a cell so we skipped the Time Out column in our example.

Fortunately, there is a way to clear certain types of data in cells. All data input by users will be constant, because it does not change unless a user manually changes it. This contrasts with formula-based cells, which are recalculated whenever the worksheet changes. Type markers are visible to the VBA engine, and you can single them out by invoking the SpecialCells property of a Range object:

Sub clear_all_contents_but_formulas()
Range(Cells(4, 1), Cells(Rows.Count, 7)).SpecialCells(xlCellTypeConstants).ClearContents
End Sub

This single line of code identifies all constant cells from column 1 to column 7, starting from the 4th row, and clears their contents. Because we told the macro to only clear xlCellTypeConstants in our range, any formulas will not be cleared and, since we used .ClearContents instead of .Clear, all cell formatting is retained.

If you have Intellisense turned on, you’ll be able to see the other types of special cells, too. If you made mistakes entering your formulas but really want to retain the constant data, you could even remove only the formulas by choosing xlCellTypeFormulas.


An Alternate "Clearing" Method

There is another method to “clear” data. This is not a true clearing operation but really an overwriting operation.

You can set the range of cells you want to clear to an empty string:

Sub clear_by_overwriting()
Range(Cells(4, 1), Cells(Rows.Count, 4)).Value = ""
Range(Cells(4, 6), Cells(Rows.Count, 7)).Value = ""
End Sub

This accomplishes the same result as .ClearContents. It preserves data validation, formatting, and preferences. You can even use it with Special Cells to match the behavior of our second ClearContents example:

Sub ovewrite_all_but_formulas()
Range(Cells(4, 1), Cells(Rows.Count, 7)).SpecialCells(xlCellTypeConstants).Value = ""
End Sub

This example clears the contents from all cells except those containing formulas. In other words, it clears the constants.

If you want to make your code more readable, you can set the cells equal to vbNullString instead of a literal empty string.

Range.Value = vbNullString

This may make your intentions easier to understsand, especially for a future debugger who may mistake the empty string assignment for a code bug. If you explicitly write out that the assignment is for a null string, it will be less confusing to him/her.

Conclusion

In this tutorial, we explained two similar built-in VBA methods for removing data from ranges in Excel. These methods are .Clear and .ClearContents. The Clear method resets your cells to the fundamental form with no text or cell formatting and no rules for data validation or conditional formatting, while the ClearContents method clears the contents of the cells but retains your formatting and any data validation rules.

This tutorial also showed you another way to “clear” contents, which involves overwriting the data already there with an empty string. This method results in the same outcome as .ClearContents on a superficial level (i.e., what you can see on the spreadsheet).

You can also single out specific types of cells for all three methods with the Range.SpecialCells property to avoid erasing certain kinds of data.

If you liked this tutorial, we have a 20+ part free email series designed to help you learn more VBA tricks like this one. Check it out using the form below and share this article on Twitter and Facebook.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I developed this unique 3-part free Excel training program to help you quickly learn VBA in a natural setting: right inside Excel. I'm also going to give you my entire personal macro library for free.

Let me join the free wellsrPRO VBA Training program