arrays, strings and more - grab a copy below.
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.
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
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
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
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.
In this tutorial, we explained two similar built-in VBA methods for removing data from ranges in Excel. These methods are
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.
Oh, and if you have a question, post it in our VBA Q&A community.
The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.