+ Reply to Thread
Results 1 to 20 of 20

Macro to delete rows where sum of values sum 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 sum of values sum to zero

    I have values in Col D and references in Col A

    Where the sum of the values sums to zero in Col D and reference is the same then rows to be deleted

    I have highlighted the values in blue where the rows are to be deleted

    I have written code that deletes the rows where a single positive and negative value matches


    Your assistance regarding this is most appreciated
    Last edited by Howardc1001; 05-26-2020 at 10:45 PM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Macro to delete rows where sum of values sum to zero

    The most important questions here are:
    - how many rows of data (maximum) you plan to process.
    - is there an upper limit of number of rows which add to 0 (or may it happen that you have say 20 rows and sum of 19 of them is 0)

    If the second answer is not a small number (7 you have shown is already not_so_small, but probably could be accepted if first number is not large).
    and especially if first number is larger than some 20 - 30
    the time to find a solution will be probably unacceptably long.
    Best Regards,

    Kaper

  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 sum of values sum to zero

    Hi Kaper

    Thanks for the reply

    The maximum number of rows to process would be 300 (it would vary each month between 150-300)
    The upper limit of number of rows which add to 0 -the maximum would be 10 (in 90 % of cases it is only 2)

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Macro to delete rows where sum of values sum to zero

    Too bad.

    If we look just on combination number of 10 elements out of 300 the number of distinct combinations would be more than 10^18 (see result of formula =combin(300,10) )
    Last reasonable time for 300 elements is probably with 4-elements subsets.

    it could be worth considering some optimization measures, but I still doubt about optimization effects when general numbers are that giant.

    I think that some trials could be done using arrays or may be dictionary, but as for me - for sure not now - I have to go to my job duties :-)

    Lets start with a small step:

    Please Login or Register  to view this content.
    of course similar approach could be used for more elements, 4, 5 but with more … nesting loops will be quite tedious. And for sure one shall not work on real cells of the worksheet but on values stored in some data structure - like I mentioned above - an array or dictionary.

  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 sum of values sum to zero

    Thanks Kaper. There is no big rush


    I have run your code and get a run time error -Type Mismatch and the code below is highlighted


    Please Login or Register  to view this content.

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Macro to delete rows where sum of values sum to zero

    ? cannot replicate the error on the sample data. Funny to notice - you have there such a triplet in rows 2;5;6 . I added a triplet to data and run the code - then noticed not only added data disappeared, so I set the breakpoint inside the deleting if and noticed the above :-)

    So may be there is a problem with the data in a file you are using it?

    See the results of run_all which runs original sub, then removing triplets then quadruples. First on sample data, then on a copy of real file. and share your observations.
    Attached Files Attached Files

  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 sum of values sum to zero

    Thanks Kaper. It runs perfectly on this workbook

    I have attached my sample data, where I amended the code as the Col is E in this case to check the values summing to zero, but it does not run


    Please test & amend
    Attached Files Attached Files

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

    Re: Macro to delete rows where sum of values sum to zero

    If I understood you right. (Try on Copy of original of "Matching Values" attachment first)
    Please Login or Register  to view this content.

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

    Re: Macro to delete rows where sum of values sum to zero

    Thanks jolivanes. It works perfectly on the original copy


    Tried to adapt it on the sample data attached , but cannot get it to work


    Kindly check & amend
    Attached Files Attached Files

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

    Re: Macro to delete rows where sum of values sum to zero

    Sorry about that.
    Change the 6 in this line to a 5.
    so this
    Please Login or Register  to view this content.
    becomes this
    Please Login or Register  to view this content.
    Mind you, with close to 900 posts under your belt, you should have been able to figure that out yourself I would have thought.

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

    Re: Macro to delete rows where sum of values sum to zero

    Thanks for the help. I did try to amend it myself, but changed the incorrect one

    It now removes all the data

    It should only remove those values in Col E where the values sum to zero
    Attached Files Attached Files

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

    Re: Macro to delete rows where sum of values sum to zero

    I am sorry but I can't follow the logic.
    Re: "It should only remove those values in Col E where the values sum to zero"
    Which values? What does "remove those values" mean?
    Show an after the fact in the same sheet so we can try to guess what needs to be done?

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro to delete rows where sum of values sum to zero

    Amazing how the criteria changes all the time...

    I just wish for once Users will upload sample files that actually mirror their actual setup...
    I've seen it happen so often that we waste valuable time on unnecessary coding...

    I have values in Col D and references in Col A
    Where the sum of the values sums to zero in Col D and reference is the same then rows to be deleted
    Really?
    Untitled.png
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

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

    Re: Macro to delete rows where sum of values sum to zero

    I have attached sample data

    please see sheet "after' This is what it should look like after delete all the values that sum to zero

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macro to delete rows where sum of values sum to zero


    Hi !

    Quote Originally Posted by Howardc1001 View Post
    […] and reference is the same then rows to be deleted
    But here again references are different …

    So like in one of your recent previous threads upon exactly the same subject - this is your third at least -
    you must extract from the main data only the rows with the 'same reference'
    via an advanced filter for example like in one of those previous threads
    to avoid a calculation time too long as Kaper yet warned here and as I did in your previous threads
    then use the 'combinatorics engine' you already had in the same previous thread
    - the one where you had help from Trebor76, sintek and me -
    as the necessary logic here is exactly the same using some arrays, a dictionary seems useless …
    Last edited by Marc L; 05-28-2020 at 08:44 AM.

  16. #16
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro to delete rows where sum of values sum to zero

    Not gonna change criteria again are you...?

    This is similar to a post Marc L & I assisted with a while back...

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 05-28-2020 at 10:16 AM.

  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 sum of values sum to zero

    Thanks for the help Sintek. I will not be changing the criteria

    I like the Icon that you linked to the macro to click on to activate the Macro

    Where is this available in Excel to insert ?

  18. #18
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro to delete rows where sum of values sum to zero

    Not in excel...Image from web...Google VBA images

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

    Re: Macro to delete rows where sum of values sum to zero

    Thanks Sintek. will Google It

  20. #20
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro to delete rows where sum of values sum to zero


+ 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 delete rows in Col B where the values are zero
    By flupsie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-22-2018, 10:13 AM
  2. [SOLVED] change macro to delete values in rows
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 06-20-2015, 07:26 AM
  3. [SOLVED] Macro help : Need a macro to delete rows that do not contain values >1 in columns O or P
    By ostego in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-13-2014, 06:44 AM
  4. [SOLVED] Help with macro to delete rows based on values
    By jimstrongy in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-26-2012, 08:49 AM
  5. Macro to delete rows containing values
    By mohan_rajun in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-12-2010, 07:30 AM
  6. Macro needed to delete rows based on values
    By bensun in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-12-2010, 06:23 AM
  7. macro to delete rows with a range for values
    By poole in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-28-2009, 02:18 PM

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