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!
User rating:
User rating:
Here is my solution:
QRID Login
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).ClearContents
The 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.
SEND BITCOIN TIPS
User rating:
User rating:
FavScripts.com is a free tool to save your favorite scripts and commands, then quickly find and copy-paste your commands with just few clicks.
Boost your productivity with FavScripts.com!
