Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 32

Thread: Clear formula cells that return 0 value

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

    Default Re: Clear formula cells that return 0 value

    @Nandy7071,

    I just had a thought that could possibly speed up your process greatly depending on how your data is laid out. You said earlier that you have 10 columns of 4500 formulas on 30 sheets. I don't know if you have other cells with either constant numbers or text on those sheets or not, but can you manually select just the columns with formula numbers only? If so, the following procedure should be quite a lot faster even though it is an entirely manual procedure. For the first sheet...

    1) Select the columns with your formula numbers in them

    2) Press CTRL+F to bring up the Find dialog box

    3) Put 0 in the "Find what" field

    4) Click the "Options>>" button and make sure the "Look in" drop down has "Values" selected and put a check mark in the checkbox labeled "Match entire cell contents"

    5) Click the "Find All" button

    6) Press CTRL+A (this will select all of the found cells)

    7) Leave the dialog box open and click the "Clear" button on the Ribbon (Home tab, Editing panel) and select "Clear All" from the drop down that appears

    Now here is where the time saver comes in...

    8) Select the next sheet and select the formula columns on it, then repeat steps 5, 6 and 7 (you do not have to repeat the earlier steps for the rest of the sheets).

    I am pretty sure the above procedure should move along at a remarkably fast pace so long as you can pick columns of data that do not contain cells with the constant (non-formula) 0 in them (they would be cleared along with the formula 0's if they were included in your selection).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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

    Default Re: Clear formula cells that return 0 value

    Couldn't you also add to the start of that to press F5, Special, Formulas, to limit your selection just formulae?

  3. #23
    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 Fazza View Post
    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)
    This worked. Reduced 85mb file down to 35mb in 1.5 hours. Great idea fazza. Thank you

  4. #24
    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
    @Nandy7071,

    I just had a thought that could possibly speed up your process greatly depending on how your data is laid out. You said earlier that you have 10 columns of 4500 formulas on 30 sheets. I don't know if you have other cells with either constant numbers or text on those sheets or not, but can you manually select just the columns with formula numbers only? If so, the following procedure should be quite a lot faster even though it is an entirely manual procedure. For the first sheet...

    1) Select the columns with your formula numbers in them

    2) Press CTRL+F to bring up the Find dialog box

    3) Put 0 in the "Find what" field

    4) Click the "Options>>" button and make sure the "Look in" drop down has "Values" selected and put a check mark in the checkbox labeled "Match entire cell contents"

    5) Click the "Find All" button

    6) Press CTRL+A (this will select all of the found cells)

    7) Leave the dialog box open and click the "Clear" button on the Ribbon (Home tab, Editing panel) and select "Clear All" from the drop down that appears

    Now here is where the time saver comes in...

    8) Select the next sheet and select the formula columns on it, then repeat steps 5, 6 and 7 (you do not have to repeat the earlier steps for the rest of the sheets).

    I am pretty sure the above procedure should move along at a remarkably fast pace so long as you can pick columns of data that do not contain cells with the constant (non-formula) 0 in them (they would be cleared along with the formula 0's if they were included in your selection).
    Going to try this out too and see if it’s faster than fazza’s idea. Will keep you posted.

  5. #25
    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
    @Nandy7071,

    I just had a thought that could possibly speed up your process greatly depending on how your data is laid out. You said earlier that you have 10 columns of 4500 formulas on 30 sheets. I don't know if you have other cells with either constant numbers or text on those sheets or not, but can you manually select just the columns with formula numbers only? If so, the following procedure should be quite a lot faster even though it is an entirely manual procedure. For the first sheet...

    1) Select the columns with your formula numbers in them

    2) Press CTRL+F to bring up the Find dialog box

    3) Put 0 in the "Find what" field

    4) Click the "Options>>" button and make sure the "Look in" drop down has "Values" selected and put a check mark in the checkbox labeled "Match entire cell contents"

    5) Click the "Find All" button

    6) Press CTRL+A (this will select all of the found cells)

    7) Leave the dialog box open and click the "Clear" button on the Ribbon (Home tab, Editing panel) and select "Clear All" from the drop down that appears

    Now here is where the time saver comes in...

    8) Select the next sheet and select the formula columns on it, then repeat steps 5, 6 and 7 (you do not have to repeat the earlier steps for the rest of the sheets).

    I am pretty sure the above procedure should move along at a remarkably fast pace so long as you can pick columns of data that do not contain cells with the constant (non-formula) 0 in them (they would be cleared along with the formula 0's if they were included in your selection).
    Ricks method was even faster. 45 minutes for the 30 sheets. 👍

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

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Nandy7071 View Post
    Ricks method was even faster. 45 minutes for the 30 sheets. ��
    Great... although I was hoping it would have been even faster than that. I am not sure this will make a difference or not, but it is something you might try... set calculations to "Manual'" before you start the procedure and turn it back to "Automatic" after the last sheet has been processed. I don't think it will speed up the overall process as all of the delayed calculation will eventually have to take place, but I am thinking it might lessen the time spent on each individual sheet and put the time delay inherent in calculating each sheet after the cells are cleared until after the last sheet has been attended to when you no longer have to "baby sit" the workbook any more (it will finish up whenever it does so, but that would not require you to sit at the workbook while it happens).

    Side Note: What is particularly annoying is the creators of Excel did not expose the code behind the "Find All" button to VBA programmers, so all the speed advantage for that operation is not available to programmers.
    Last edited by Rick Rothstein; Jul 5th, 2019 at 06:14 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #27
    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
    Great... although I was hoping it would have been even faster than that. I am not sure this will make a difference or not, but it is something you might try... set calculations to "Manual'" before you start the procedure and turn it back to "Automatic" after the last sheet has been processed. I don't think it will speed up the overall process as all of the delayed calculation will eventually have to take place, but I am thinking it might lessen the time spent on each individual sheet and put the time delay inherent in calculating each sheet after the cells are cleared until after the last sheet has been attended to when you no longer have to "baby sit" the workbook any more (it will finish up whenever it does so, but that would not require you to sit at the workbook while it happens).

    Side Note: What is particularly annoying is the creators of Excel did not expose the code behind the "Find All" button to VBA programmers, so all the speed advantage for that operation is not available to programmers.
    I will try that out next time. The find part only takes about 10 sec. The part of the process that took the longest is the CTL+A selecting all the fields which takes approximately 20-30 sec on one sheet.

    Next time I will try selecting all the columns for all 30 sheets and running the find and clear operation on all of them at once. That should speed it up as long as the program doesn’t crash

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

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Nandy7071 View Post
    Next time I will try selecting all the columns for all 30 sheets and running the find and clear operation on all of them at once. That should speed it up as long as the program doesn’t crash
    You cannot do that because you can only select cells on the active sheet.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #29
    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
    You cannot do that because you can only select cells on the active sheet.
    Ok. Would’ve been nice but I don’t mind doing it sheet by sheet.

    Thanks again for your help. Im loving this forum!

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

    Default Re: Clear formula cells that return 0 value

    Quote Originally Posted by Nandy7071 View Post
    This worked. Reduced 85mb file down to 35mb in 1.5 hours. Great idea fazza. Thank you
    I wonder if that was using VBA?
    To receive a better answer, put more work into asking the 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
  •