Jul 15 2021 08:16 AM
I'm using the Remove Duplicates function on simple data sets (usually 20-40 items). Excel fails to remove duplicates, and the feedback received in 'completed' dialogs doesn't seem to be consistent or correct.
In video clip (<1:30), I created a new document with simple data. Blocks of data are simply copy/pasted to create the duplicates. I select the columns to analyze, and results are shown, clearly removing some duplicates but not all, even after multiple runs.
I'll start with the question
"What am I not doing right?"
I've also tried
Jul 15 2021 08:29 AM
In the Remove Duplicates dialog, you leave all columns checked. As a consequence, Excel will look for rows that are duplicate in all columns A to D. The 12 rows that it reports as removed each time are the empty rows at the bottom.
If you want to remove entire rows if column A is duplicate, select columns A to D (or the entire sheet) and clear the check boxes for columns B, C and D in the dialog.
If you want to remove duplicates from each individual column, select each column in turn and remove duplicates.
Jul 15 2021 08:43 AM
Jul 15 2021 12:12 PM
Do you want to clear the cells with duplicates, or delete them and move the cells below upwards, or delete them and move the cells to the right leftwards, or ...?
Jul 16 2021 09:47 AM
Jul 16 2021 11:26 AM
SolutionRun this macro. Please test on a copy of your data first.
Sub RemoveDups()
Dim rng As Range
Dim r As Long
Dim m As Long
Dim c As Long
Application.ScreenUpdating = False
m = Range("A:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rng = Range("A2:D" & m)
For c = 4 To 1 Step -1
m = Cells(Rows.Count, c).End(xlUp).Row
For r = m To 2 Step -1
If Application.CountIf(rng, Cells(r, c).Value) > 1 Then
Cells(r, c).Delete Shift:=xlShiftUp
End If
Next r
Next c
Application.ScreenUpdating = True
End Sub
Jul 16 2021 03:00 PM
Jul 16 2021 11:26 AM
SolutionRun this macro. Please test on a copy of your data first.
Sub RemoveDups()
Dim rng As Range
Dim r As Long
Dim m As Long
Dim c As Long
Application.ScreenUpdating = False
m = Range("A:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rng = Range("A2:D" & m)
For c = 4 To 1 Step -1
m = Cells(Rows.Count, c).End(xlUp).Row
For r = m To 2 Step -1
If Application.CountIf(rng, Cells(r, c).Value) > 1 Then
Cells(r, c).Delete Shift:=xlShiftUp
End If
Next r
Next c
Application.ScreenUpdating = True
End Sub