Code to delete cell contents IF the result of a formula in the cell = 0

I am very new to VBA so please be gentle with me

I have a worksheet that already contains a “Private Sub Worksheet_Change(ByVal Target As Range)” procedure (set up by someone else).

I now want to add into the worksheet some code that automatically deletes cell contents IF the result of a formula in the cell = 0. The formula in the cell is =IF(A2<>"",A2-C2," ") and the formula is continued down column D. I don’t know whether this can be added onto the existing procedure or if it should be a new one.

The reason I want the cell contents to be deleted is if the formula result is 0 then I don’t want the 0 to count in a Pivot Table I run from the database.

Some additional info (in case it makes a difference) is that a figure will be entered into column A initially and then at a later date another figure could be entered into column C that produces the 0 result in D. At this point I want the cell contents cleared.

I hope this makes sense. I have attached a very simple example.

Example.xlsx

Thanks in advance, Janet

try this..

Please Login or Register  to view this content.

Thank you - I appreciate the reply. That certainly works & clears all the cells in column D with a 0 result if I run the code. What I was really hoping for though is code that will automatically clear the cell contents as soon as they result in 0 (i.e. as it happens and without having to maually run the code). Any further ideas on that basis?
Thanks again.

=if(a2<>"",a2-c2,"0")

Thank you. I assume what you are saying here is to enter this as a formula D, but that just "hides" the 0 result. It still counts as a number as the cell is not really blank - it just looks like it is.

There is no way to remove a formula unless you use a macro to run it. you can set auto run macro to help u achieve your result once you open the worksheet.

1. Go to the Visual Basic Editor by pressing the keys Alt+F11.

2. From the Project Explorer, double-click over the ThisWorkbook object

3. That will execute an event window for you. At the top of the new code window, you will see the word 'General'. Select the drop down where you see general and select 'Workbook'.

4. You will notice that Excel VBA assumes the Open event. That means whatever code you insert in this procedure will execute automatically when the workbook is opened.

Please Login or Register  to view this content.

That certainly works and deletes any 0 formula results when the workbook opens! Thank you very much for your patience - I really appreciate it

you are welcome. (: