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.
- Clear Contents while Retaining Formulas
- An Alternate “Clearing” Method
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:
Here, we have formulas in the
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
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
.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.
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, 