delete entire row if cell value is the same as the cell above

  • Hi guys,


    I would like to create a macro/VBA that allows me to delete and entire row only if the cell value is the same as the cell value above.


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Type

    [/td]


    [td]

    Price

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    12.00

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    12.00

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    15.00

    [/td]


    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    20.00

    [/td]


    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    20.00

    [/td]


    [/tr]


    [/TABLE]


    So in this table I will add buttons that will allow rows to be deleted individually only if the cell value in column 'Type' is the same as above. Also the range of column 'Type' is unknown.


    Hope you can help?!


    Cheers
    AMQ

  • Re: delete entire row if cell value is the same as the cell above


    Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: delete entire row if cell value is the same as the cell above


    use one of the 2 codes
    suppose your data is in column A & B

    Code
    Sub remove_rows()
    lr = Cells(Rows.Count, 1).End(3).Row
        Range("A1:b" & lr).RemoveDuplicates Columns:=1, Header:=xlYes
    End Sub


    or if you want to do this by selection

    Code
    Sub remove_rows1()
        Selection.RemoveDuplicates Columns:=1, Header:=xlYes
    End Sub
  • Re: delete entire row if cell value is the same as the cell above


    Mumps - I'm sorry your code deletes all the rows from my table!


    Salim Ahmad - on the right track but your code deletes all duplicates. I need the code to delete just one row only if it is a duplicate of the row above, so I have at least one unique record in the table. I think it may just be a little amendment but you guys will know better.:)


    So in my table I don't mind having duplicate data but I would like the user to have the ability to remove one row of data at a time only if there is a duplicate value in column 'Type'


    Also upon deleting the rows I would like the remained of the table to move up


    Thanks

  • Re: delete entire row if cell value is the same as the cell above



    use for that this code code

  • Re: delete entire row if cell value is the same as the cell above


    almost..... :?


    The rows at the top of the sheet are being deleted!!!


    My table starts at cell ("C5") and continues down to an unknown number of rows.

  • Re: delete entire row if cell value is the same as the cell above


    AMQ

    Quote

    almost..... :?


    Where in your post 1 you say that your data start in C5
    We have to guess how you got data and offer you a solution?
    Code of Mumps is very good, only be amended depending on what you've remembered later, ie:

    Quote

    My table starts at cell ("C5") and continues down to an unknown number of rows.

  • Re: delete entire row if cell value is the same as the cell above


    A valid point was made - you posted a generic description of your data and then practically dismiss replies when you get generic code to do (generally) what you asked. Also, bear in mind Mumps is a long term poster here with many hundreds of posts, the vast majority (if not all) answers rather than questions. His code, as posted, works. I haven't tested Salim Ahmad's code, your response to that proves it (generally) works.


    If you had specific requirements they should have been stated.

  • Re: delete entire row if cell value is the same as the cell above


    Try this macro:

    If it still doesn't work, please post a copy of your file. Working with tables requires a little different coding.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: delete entire row if cell value is the same as the cell above


    Thanks Mumps - I still cant get it to work with my table.


    Unfortunately I have sensitive data so i cant upload the original table but i do have a replica attached here. I hope this helps.


    You will see there is an insert button which allows the user to insert multiple rows which is why upon deleting i say there is an unknown number of rows.


    Thanks for your time on this x


    forum.ozgrid.com/index.php?attachment/67019/

  • Re: delete entire row if cell value is the same as the cell above


    Mumps,


    How can your macro be modified to delete the row based on date...for instance, delete the rows with the older dates while keeping the most recent?

  • Re: delete entire row if cell value is the same as the cell above


    AMQ: Try this macro:

    It works with the sample file you posted. If your actual data is organized differently or is not located in the same range, the macro will have to be modified to match your actual data.


    @Yeppy: It's against Forum rules to post your question in someone else's thread. Please start your own thread. If you send me a Private Message with the link to your thread, I'll be happy to look at it.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: delete entire row if cell value is the same as the cell above



    Thanks Mumps


    Running that code on the sample file deletes all duplicates from the table. Is there any way of keeping some duplicates so that the user only deletes one row at a time only if it has a duplicate. Therefore keep a unique value in the 'Type' Column.


    So the table can have 5 A's, 6 B's and 7 C's
    The user can delete 1, 2, 3 or 4 rows that have a value A but can never delete 5 rows that contain the value A. This is not to say there will only ever be 5 rows with the value A... So some how if there is a way of using .Find perhaps to find the total number of A's and make sure one always remains? i hope this makes sense, I'm afraid I'm not very good at explaining at times...


    Do you think the code can be amended to compensate for this?


    Cheers,
    AMQ

  • Re: delete entire row if cell value is the same as the cell above


    I'm not really sure what you mean. The code I suggested will always leave one row of each unique value. How would the user determine how many rows of each type to delete?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: delete entire row if cell value is the same as the cell above


    Quote

    deletes all duplicates from the table.


    Your exact request in post #1 was

    Quote

    delete and entire row only if the cell value is the same as the cell value above.

    This is exactly what Mumps' code has accomplished.


    Whilst this is a free help forum and most members here will go out of their way to help you, you need to help them in doing so by clearly stating your full requirements from the start and providing, at the very least, a sample of the data and what the expected outcome should look like.


    This should all be done in your original question - currently your requirements are changing which is understandably going to cause some frustration for those trying to help. Your last request

    Quote

    Is there any way of keeping some duplicates

    almost directly conflicts your original requirement.


    If the code you asked for originally has been supplied and is working, but the requirements have now changed, then please start a new thread for the new issue so that a logical set of requirements are maintained throughout the thread.


    If you have any questions on any of the above, please feel free to ask.


    Regards,


    S O

  • Re: delete entire row if cell value is the same as the cell above


    Thanks again Mumps for your patience. I realise I'm not very good at explaining what exactly I anticipated to happen when I ran the code - apologies for the confusion and frustration this has caused. However your code works as an excellent reset feature to what I was trying to achieve!! 8-)


    Thank you to all others who contributed also. Your feedback and comments are appreciated.


    Cheers
    AMQ

  • Re: delete entire row if cell value is the same as the cell above


    Glad it worked out.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!