ents 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.