VBA Macro Macro for deleting rows with same reference and opposite signs

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    crossposted: https://www.mrexcel.com/board/…d-opposite-signs.1184201/

    Good day


    Could anyone help with a macro to delete rows, condition have the same reference and two exact numbers but of different signs (opposite).


    Thank you in advance for your time :thumbup:

  • Good Day gijsmo!


    Thank you for you reply, the attached example that you provided worked just fine! Based on the information that was on the sheet. Although when I try to run the macro with a different information nothing gets deleted.

    Could you please consider the sheet attached with a more complex information.

    Thank you!

    Files

    • DelRows v1.xlsm

      (176.73 kB, downloaded 19 times, last: )
  • That error could be because no cells match the criteria. SpecialCells will produce an error if this occurs so error handling is necessary.


    Attach an example workbook not images.

  • As I indicated in my earlier example, the code worked with the original data provided.


    No error handling should be necessary because the SpecialCells in this case was based on the unique values in Col G in the original sample.


    The new data you provided however uses Col F instead of G and it includes 'unformatted' data such as "1.46505E+14" which needs special handling.


    The change from Col G to Col F and the 'unformatted' values now included in Col F should be able to be handled by the attached revised example. This currently works on the sample provided, there are on guarantees if this data is not representative of the actual data or if, for example, the data format changes.


    DelRows v2.xlsm

  • I would normally use error handling as well however in this specific case it should not be required because the autofiltering was based on an array of unique values derived from the data in the column being filtered

  • Greeting

    The code is running smoothly. It is so precise.

    However when it encounters 3 lines with the same references, it wont delete any rows even if 2 of them null each other, but after i remove the one that doesnt has a match and click on the macro again it deletes the pair.

    Sometimes I get "overflow runtime error 6" (thats the least of my problems) if I remove some lines (reduce) and click on the macro again, runs fine.


    IM VERY HAPPY :) I will even sleep tonight!!

    Cant thank you enough mates, that piece of code will help me a lot!:)


    I will just continue to analyze the code, but I guess theres nothing else to be done.

  • Try this alternative approach, file attached


    Code assigned to the button

    Files

    • DelRows v3.xlsm

      (285.95 kB, downloaded 23 times, last: )

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Greeting mates!

    Thank you all that got involved, cant thank you enough.

    gijsmo your lines and the comments were spetacular!

    KjBox thank you for the improvement, the code is lighter and working perfectly!!

    Someone please give this guys a medal!


    My problem is solved!