How to clear all the data in the current active Excel sheet but keep formula definitions intact? I.e. if I have a table with some columns calculated by formulas, and some other columns containing data loaded from an external SQL database, how do I write a VBA macro that would re-load the data? One of the first steps I would need to do is to clear all the existing data, but preserve all the formula definitions.
Looking for a simple code to clear the data cell values but keep formulas defined in that same range. Thank you!
Here is my solution:
ActiveSheet.Cells gives us the
Range object that contains the entire active worksheet. Then we use the Range.SpecialCells method that will help us to select cells with data. This is because we are passing the
xlCellTypeConstantsparameter (decimal value 2). It will return "Cells containing constants", i.e. cells with data. I've tested this code and it works as expected. It clears all the cells with data in the active worksheet, and it also preserves the formula cells. Formula cells are re-calculated automatically once the new data have been loaded into the cells.