Clear formula cells that return 0 value
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 32

Thread: Clear formula cells that return 0 value

  1. #11
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,982
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Clear formula cells that return 0 value

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

  2. #12
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,134
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Clear formula cells that return 0 value

    i think, Rick, you missed Marcelo's post #9 ...
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


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

    Default Re: Clear formula cells that return 0 value

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

  4. #14
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,134
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Clear formula cells that return 0 value

    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


  5. #15
    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
    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

  6. #16
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,982
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Nandy7071 View Post
    Tried to run it on 1 sheet and 3 hours later still processing
    Is my code processing faster than that?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #17
    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
    Is my code processing faster than that?
    That’s running it with your code. I think it’s just too many formulas too handle

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

    Default Re: Clear formula cells that return 0 value

    Have you made sure to disable calculations before running?

  9. #19
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,982
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Clear formula cells that return 0 value

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

  10. #20
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,134
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Clear formula cells that return 0 value

    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)
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


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
  •