Hello, Excellers. Welcome back to another #Exceltip, #MacroMonday blog post in my Excel series. Do you need to delete some data from your spreadsheet but keep the formula for future use? Learn how you can do it with a simple Excel tip. This Excel tutorial will show you how to delete data but keep formulas in Excel. Yes, that is correct. This code will delete constants but keep your formulas.
I also have a tutorial on how to delete cell data without writing a macro. You can read the alternative version here.
Preparing To Write The Macro To Delete Data From Cells, But Keep Formulas.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Hitting ALT +F11 or
- Select the Developer Tab | Code Group | Visual Basic.
Both methods have the same result. It is just personal preference.
Then, create a module to store your code in your Personal Macro workbook or a new module. What’s the difference?.
If I save the macro in my Personal Macro workbook, it will be available for use in any of my Excel workbooks. However, if stored in the current workbook, then use is restricted to that workbook. As I will probably reuse this code repeatedly, it makes sense to save it in my Personal Macro Workbook.
If you want to read more about your Excel PMW, check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Step 1. Name the Macro.
The first step after inserting a new module is to name the macro. Type Sub then the macro name. There are a few guidelines on macro names. If you need to review these guidelines, then read my blog post here. In this example, I have named the macro ClearDataCells.
Sub ClearDataCells()
End Sub
Excel automatically inserts the End Sub code. Insert all other lines of code between these two lines.
Step 2. Using Special Cells To Delete Data.
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).ClearContents
So, the second step and the only line of code is to use the Range.SpecialCells method. This aids in selecting cells with data. The syntax of Range.SpecialCells is below
expression.SpecialCells (Type, Value)
expression -A variable that represents a Range object.
The ActiveSheet.Cells give the Range Object. The Range.SpecialCells will allow the selection of cells with data using the xlCellTypeConstants parameter.
Step 3. End The Macro after deleting data but keeping formulas.
End Sub
The final step in this code is to end the macro. The End Sub line of code already exists from the naming of the macro in the first line of code. So, this is an alternative way to delete data but keep formulas in Excel.
To delete data without deleting formulas in Excel, use the macro. It is a simple process that takes just seconds to set up and can save you hours of work. If you need help with macros or want more information on this topic, let us know!
Further Recommended Reading.
Finally, if you want even more Excel or Macro Tips then read my complete list of Macro Monday and Formula Friday Blog Posts all in one place. Don’t forget to bookmark the links as I update them every week.
