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.

Quote from Mumps

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?

Quote from Mumps

Try:

Code

Sub DeleteRows()
Application.ScreenUpdating = False
Dim bRow As Long, aRow As Long
Dim counter As Long
bRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheets("Sheet1").Cells.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
aRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
counter = bRow - aRow
MsgBox (counter & " rows were deleted.")
Application.ScreenUpdating = True
End Sub

Display More

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?