Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: Clear formula cells that return 0 value

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Clear formula cells that return 0 value

    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.

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,429
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clear formula cells that return 0 value

    Try this code.

    Code:
    Range("A1").CurrentRegion.SpecialCells(xlCellTypeFormulas).Value = 0
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,316
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by lrobbo314 View Post
    Try this code.

    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...
    Code:
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear formula cells that return 0 value

    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?

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,316
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Nandy7071 View Post
    My workbook has approximately 1.5 million formula cells.
    Gulp! That is a lot of formulas.



    Quote Originally Posted by Nandy7071 View Post
    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)...
    Code:
    Sub DeleteFormulaZeros()
      Dim Ar As Range, Cell As Range
      Application.ScreenUpdating = False
      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.ScreenUpdating = True
    End Sub
    Last edited by Rick Rothstein; Jul 5th, 2019 at 01:35 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,273
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Clear formula cells that return 0 value

    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.

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Rick Rothstein View Post
    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)...
    Code:
    Sub DeleteFormulaZeros()
      Dim Ar As Range, Cell As Range
      Application.ScreenUpdating = False
      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.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!

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,316
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Nandy7071 View Post
    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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,273
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Rick Rothstein View Post
    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.

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Rick Rothstein View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •