Counting and deleting duplicate entries over multiple columns

  • Hi ladies and gents,


    I have this task I am working on where I have a data set of around 58,00 rows and 21 columns.


    My task is to count and delete any duplicate rows. The rows need to match the previous row in columns 1, 2, 3, 4, 5, 6 and 7.


    I have looked around and have found lots of VBA codes which work great but only check over a single column.


    I have also done it the COUNTIFS way which works but it a little messy for my liking.


    I know there is a Remove Duplicates function in excel but I need it to output the total number of duplicates into a cell.


    Also since its quiet a large macro, a status bar would also be great just to know where its up to.


    Any help, idea or even a link to a thread that's relevant would be greatly appreciated.

  • Try on a copy of your file:

    Code
    Sub DeleteRows()
        Application.ScreenUpdating = False
        Sheets("Sheet1").Cells.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
        Application.ScreenUpdating = True
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Try on a copy of your file:

    Code
    Sub DeleteRows()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Cells.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
    Application.ScreenUpdating = True
    End Sub


    That code works like a charm. Really fast and efficient, thank you.


    Is there a way it can also count the number of rows being deleted?

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Try:


    That is perfect thank you, you are an absolute genius. Everything I have tried before was super slow and would take ages but this is just fantastic.


    Is there a way to adapt this code so it only counts the duplicates without deleting them too?

  • Thank you for the feedback. :) There may be a way to count the duplicates rows without deleting them, but unfortunately, I'm not aware of it. Perhaps you could start a new thread and pose that question. There may be someone out there who knows the answer. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: 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!