Clear formula cells that return 0 value

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Nandy7071

New Member
Joined
Jun 27, 2019
Messages
16
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,037
Office Version
2010
Platform
Windows
Have you made sure to disable calculations before running?
That is a good thought...
Code:
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,053
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top