How to count and delete duplicate entries over multiple columns
Category: [Excel] Demo Available
How to count and delete duplicate entries over multiple columns
Requirement:
The user this task where the user has a data set of around 58,00 rows and 21 columns.
The user's 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.
The user has looked around and have found lots of VBA codes which work great but only check over a single column.
The user also done it the COUNTIFS way which works but it a little messy for my liking.
The user knows there is a Remove Duplicates function in excel but needs 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.
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to count blanks if 1 of 2 conditions are met |
| How to count weekdays in a month excluding holidays |
| How to use Excel MID() but counting from Right |
| How to COUNTIF using input cell as range depth |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.