Find cells referring to blank cells

What is the best way to find cells whose formulas refer to blank cells in Excel VBA. I'd like to delete any cell that references a blank cell.

More concretely, I have a two sheets: One sheet contains the actual values:

Product   Month1  Month2 Month3
Sa开发者_开发知识库mple1     1        3     5
Sample2     5        7     6
Sample3     3        8     2

The other is a summary view, with formulas to sum up the values, with the following formulas:

Product       Month1
=values!A2    =SUM(values!B2:D2)
=values!A3    =SUM(values!B3:D3)
=values!A4    =SUM(values!B4:D4)
=values!A5    =SUM(values!B5:D5)

TOTAL         =SUM(values!B:D)

Now in the previous example, the last raw refers to a blank row, namely the fifth row. Excel will show those cells as "0". Is there a mechanism to delete those cells within VBA?

Please note I prefer deleting the rows, to keep the TOTAL row close to the actual last value. Otherwise, the Total row might be distant from the rest of the values. Also, having blank cells with formulas may lead to a large Excel file.

EDIT: Clarified the question to role out the keeping the cells blank.

---

Is it always the last row, that could evaluate to 0? If so u Could use a IF statement like:

=IF(SUM(values!B2:D2) > 0 ,values!A2,"") =IF(SUM(values!B2:D2),SUM(values!B2:D2),"")

No VBA needed...

---

I think Autofilter is the way to go here. If there's a zero in column A, I'm guessing you want to hide that whole row. You say delete, but I wonder if hide is a better way.

Put a filter on the range and for column A select everything except 0.

---

You could do this without having to write code to delete rows. I would use a variant of Arnoldiuss' solution.

For the Month total use:

=IF(LEN(values!A2)>0,SUM(B2:D2),"")

In this way, you can simply fill-down all the formulas and not have to worry if you reference a non-existent product.

---

Based on your edit i guess a pivot table fits your needs. Add the products to the rowlabels and add the following calculated field to the values

=SUM(Month1,Month2,Month3)

Then add a value filter > 0

I would not recommend deleting rows in the "formula worksheet", for future use the series would wrecked, because of the missing references.