st 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.
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,
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.
Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit full of pre-built macros so you can master file I/O, arrays, strings and more - grab a copy below.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit full of pre-built macros so you can master file I/O, 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 ClearCont