+ Reply to Thread
Results 1 to 21 of 21

Macro to Delete Rows where Value sums to Zero

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Macro to Delete Rows where Value sums to Zero

    I have balances in Col H. Where the narrative has a common item for eg Fuel and the balances for fuel sum to zero, then the rows containing fuel to be deleted


    I am looking for code delete rows where the balance in Col sums to zero for Fuel & Stationery Contained in Col I


    Your assistance in this regard is most appreciated

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Macro to Delete Rows where Value sums to Zero

    Please Login or Register  to view this content.

    You only need it if Column I has "Stationary" or "Fuel" '<-----Spelling different in your attachment
    Please Login or Register  to view this content.
    Last edited by jolivanes; 08-14-2021 at 12:04 AM.

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Thanks for the help. The Items that contains Fuel and Stationery In Col I where the values for these items sum to zero in Col H are not all being deleted

    I have highlighted the items to be deleted so you can amend your code accordiingly

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Macro to Delete Rows where Value sums to Zero

    Re: The Items that contains Fuel and Stationery In Col I where the values for these items sum to zero in Col H are not all being deleted
    "Fuel and Stationery" or "Fuel or Stationery"? One or the other or both?
    Re: values for these items sum to zero in Col H
    Which 2 Columns have to sum to zero? (There is no zero in Column H anywhere)

    Your attachment shows to delete all rows where there is a zero (0) in Column G and/or "Fuel" in Column I and/or "Stationery" in Column I
    Is that how it should work?

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Thanks for the Reply

    Sorry for any confusion

    1) Where "Fuel" is in Col I and the values in Col G for Fuel sums to zero then all the rows for "Fuel" that sums to Zero to be deleted -see items in Yellow
    2) Where "Stationery" is in Col I and the values in Col G for Stationery sums to zero then all the rows for "Stationery" that sums to Zero to be deleted -see items in Red

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Macro to Delete Rows where Value sums to Zero

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Many Thanks for the help

    Code works perfectly

  8. #8
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Hi Jolivanes

    Should I need to include more items for eg "Cell"', "Phone" Etc as an array, kindly advise how to amend code below


    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Macro to Delete Rows where Value sums to Zero

    I'll have a look at it later as it is bedtime for me here.

  10. #10
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    No Problem. Whenever you get a chance is fine

    I have tried to amended your formula without an array, but get a syntax error. I have included all possible items to be included in the array

    Please Login or Register  to view this content.

  11. #11
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro to Delete Rows where Value sums to Zero

    Hi Howardc1001,

    Try below code ... You can add more words in the "a" Array which should be easier to maintain than changing the code itself.

    Also, if my understanding is correct, you want only those items where the net is = to zero to be deleted. i.e. if the sum of all items containing the word "Stationery" is equal to zero then all rows to be deleted otherwise don't delete. Based on your sample file, just change the amount from debit to credit for reference # 9683263 & the balance for that row becomes -30 then none of the "Stationery" rows should be deleted. This is what my code does which is unlink the behavior of the code supplied by jolivanes. Not sure which one you're looking for ...
    Please Login or Register  to view this content.
    Last edited by nankw83; 08-14-2021 at 04:54 AM. Reason: Added sheet reference
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  12. #12
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Thanks nankw83


    Many thanks for the help, your code works perfectly

  13. #13
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Hi Nankw83

    I have tried your code on other sample data that has an additional column with data

    I tried to amend your , but cannot get it to work


    Kindly test & amend

  14. #14
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro to Delete Rows where Value sums to Zero

    Based on the attached file in post #13, change below numbers highlighted in red
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Thanks for the Help

    I have tested the code and it is deleting all the items

    The code most only delete those rows for each category in Col I for eg if Col I contains "Fuel" and the sum of "Fuel" in col H sums to zero, then delete all rows pertaining to "Fuel", if Col I contains "Cell" and the sum pertaining to Cell sums to zero, then delete those rows which sums to zero for "Cell" etc


    I have highlighted the items in Red as those categories do not sum to zero based in the narrative

    Kindly amend your code accordingly

  16. #16
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro to Delete Rows where Value sums to Zero

    I was running out & didn't test it well, one more thing to change

    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Many Thanks nankw83

    I should have picked this up as well

    Code works 100%

  18. #18
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Hi nankw83

    I tested your code on sample attached. The Narrative in Col I contains Fuel and all the Fuel Items sum to zero but when running the macro, the rows containing these items are not deleted


    Your code worked perfectly on my other sample data but when testing it on these items, it is not working


    Kindly test & amend

  19. #19
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro to Delete Rows where Value sums to Zero

    The code has failed as the SumIf is not exactly zero, it is coming as -4.54747350886464E-13. I am not sure but I did a quick search & it seems that is an excel issue "non-integers cannot be represented exactly in 64-bit binary floating-point" ... Anyway, to overcome this just add the text in red in the below line
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to Delete Rows where Value sums to Zero

    Many thanks for the help and advise

    Code works perfectly

  21. #21
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Macro to Delete Rows where Value sums to Zero

    Glad to help & thanks for the added rep+

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to Clear Rows Where Total In Col D sums to Zero
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-08-2021, 11:11 AM
  2. [SOLVED] Macro to filter data by category and delete rows where category sums up to zero
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-02-2021, 12:57 PM
  3. [SOLVED] Macro to Delete Delete headings and 9 rows above from row 15 onwards
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2021, 12:06 AM
  4. Delete sums that add to 0 inside a bigger summatory
    By Kyrpo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2015, 12:28 PM
  5. Macro To Find Rows With Duplicates, Compare Cells, And Delete Rows. - Excel
    By Kwame001 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2013, 10:41 AM
  6. Macro to delete certain columns and delete rows based on time in another column
    By beepbeep27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2012, 11:47 AM
  7. Replies: 13
    Last Post: 08-23-2011, 11:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1