Clear Excel DATA but preserve FORMULAS using VBA macro

$0
=
$0
+
$0
No specific Bitcoin Bounty has been announced by author. Still, anyone could send Bitcoin Tips to those who provide a good answer.
0

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!

Tags: , ,

1 Answer - total earned - 0 BTC ($0 USD)

1
=
0
=
$0
Internet users could send Bitcoin Tips to you if they like your answer!

Here is my solution:

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
1

Post Answer