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.

 

[lrobbo314]

Try this code.

Range("A1").CurrentRegion.SpecialCells(xlCellTypeFormulas).Value = 0

 

[Rick Rothstein]

Try this code.

Range("A1").CurrentRegion.SpecialCells(xlCellTypeFormulas).Value = 0

I read the OP's question differently than you did. It seems to me that the OP wants to clear all formula cells that evaluated to 0. Here is a macro that does this...

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

It is also not clear to me whether the OP knows how to use macros or not, so I am including the following instructions for him...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (DeleteFormulaZeros) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Nandy7071]

Thanks for the reply Irobbo, Rick.

Your correct Rick, my intention is to clear cells which evaluated to 0. This is my first time using macro (hearing about it too) but your instructions are very easy to follow.

Currently running your code on my workbook and it looks like its gonna take a while to complete. My workbook has approximately 1.5 million formula cells. Would there be a way to run the code only on highlighted cells?

 

[Rick Rothstein]

My workbook has approximately 1.5 million formula cells.

Gulp! That is a lot of formulas.

Would there be a way to run the code only on highlighted cells?

By "highlighted cells" do you mean cells that have been selected? If so, here is my code modified to only look at cells within the current selection (the only change I made from my previous code is highlighted in red)...

Sub DeleteFormulaZeros()
  Dim Ar As Range, Cell As Range
  Application.ScreenUpdating = False
  On Error Resume Next
  For Each Ar In [B][COLOR="#FF0000"]Selection[/COLOR][/B].SpecialCells(xlFormulas).Areas
    For Each Cell In Ar
      If Cell.Value = 0 Then Cell.Clear
    Next
  Next
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[Marcelo Branco]

Rick,

I'm not sure, but would not this decrease the number of cells to be compared with 0?
For Each Ar In Selection.SpecialCells(xlFormulas,xlNumbers).Areas

M.

 

[Nandy7071]

Gulp! That is a lot of formulas.




By "highlighted cells" do you mean cells that have been selected? If so, here is my code modified to only look at cells within the current selection (the only change I made from my previous code is highlighted in red)...

Sub DeleteFormulaZeros()
  Dim Ar As Range, Cell As Range
  Application.ScreenUpdating = False
  On Error Resume Next
  For Each Ar In [B][COLOR=#FF0000]Selection[/COLOR][/B].SpecialCells(xlFormulas).Areas
    For Each Cell In Ar
      If Cell.Value = 0 Then Cell.Clear
    Next
  Next
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

Bingo. That's exactly what I was looking for!

Didnt realize I had so many formulas until now. 4500 rows, 10 columns and 30 sheets.

I cant thank you enough Rick, you helped me save alot of time. I used to spend a whole day clearing rows that evaluated to 0 in order to reduce the file into a manageable size. Im glad with your help those days are over!

 

[Rick Rothstein]

Bingo. That's exactly what I was looking for!

Didnt realize I had so many formulas until now. 4500 rows, 10 columns and 30 sheets.

I cant thank you enough Rick, you helped me save alot of time. I used to spend a whole day clearing rows that evaluated to 0 in order to reduce the file into a manageable size. Im glad with your help those days are over!

Marco has raised an interesting question and I am not sure of the answer to it. Do you have any formulas anywhere on your sheets where the result is not a number? If so, is that why you asked about processing a selection... so that you could select around formulas returning text?

 

[Marcelo Branco]

Marco has raised an interesting question and I am not sure of the answer to it. Do you have any formulas anywhere on your sheets where the result is not a number? If so, is that why you asked about processing a selection... so that you could select around formulas returning text?

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

M.

 

[Nandy7071]

Marco has raised an interesting question and I am not sure of the answer to it. Do you have any formulas anywhere on your sheets where the result is not a number? If so, is that why you asked about processing a selection... so that you could select around formulas returning text?

It’s all numbers