Clear formula cells that return 0 value

[Nandy7071]

Is there a way to clear all cells which have formulas but return 0 value? Im trying to reduce the file size of my spreadsheet.

Ive seen some suggestions to use "Find and Replace" and replace all cells from "0" to "" but Excel doesn't allow find and replace on values, only formulas.

 

[Rick Rothstein]

It’s all numbers

Good, then what I gave you will not cause any problems. You could use what Marcelo posted, but I don't think it would change anything speed-wise.

 

[Fazza]

i think, Rick, you missed Marcelo's post #9 ...

 

[Rick Rothstein]

Once I made a mistake and used Rich instead of Rick...and you complained...
Is it a revenge?
(just kidding )

No Marcel, it was not revenge.

 

[Fazza]

 

[Nandy7071]

Good, then what I gave you will not cause any problems. You could use what Marcelo posted, but I don't think it would change anything speed-wise.

Tried to run it on 1 sheet and 3 hours later still processing

 

[Rick Rothstein]

Tried to run it on 1 sheet and 3 hours later still processing

Is my code processing faster than that?

 

[Nandy7071]

Is my code processing faster than that?

That’s running it with your code. I think it’s just too many formulas too handle

 

[RossTattersall]

Have you made sure to disable calculations before running?

 

[Rick Rothstein]

Have you made sure to disable calculations before running?

That is a good thought...

Sub DeleteFormulaZeros()
  Dim Ar As Range, Cell As Range
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  On Error Resume Next
  For Each Ar In Selection.SpecialCells(xlFormulas).Areas
    For Each Cell In Ar
      If Cell.Value = 0 Then Cell.Clear
    Next
  Next
  On Error GoTo 0
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

 

[Fazza]

need some more knowledge on what is being done to know if this would be OK,

faster if suitable would be to sort the data so that all the zero values are in one block
identify that block, and clear all in one hit
if needed, sort again to original order (may need temporary field for this to know the original order)