Removing duplicates


Board Regular
Oct 14, 2005

I currently have a spreadsheet where I have duplicate records in some of the rows. Is there an easy way that I can have only 1 of each record in the spreadsheet without having to delete them manually?

I have recently been using the AutoFilter to do this, but it's still very time consuming when you have thousands of records to deal with!!


Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi Paul,

One way would be to use advanced filter - tick the 'Unique Records' box & copy to another location
Upvote 0

Is your data like List 1 where there is just a single column to determine duplicates?

Or like List 2 where multiple columns need to be checked to determine if the row is a duplicate?
Mr Excel.xls
1List 1List 2
Delete Duplicates
Upvote 0

Alan's method should work fine.

However, if you want an alternative to consider, here is one. You mentioned that using AutoFilter for this task was time-consuming with lots of records. Here is a fairly quick way using AutoFilter.

1. Assuming your list is in column A starting in row 2, in B2 put =COUNTIF(A$1:A2,A2)=1
2. Copy this down the whole length of your data. If your data has no gaps (blank rows) this can be done very quickly by selecting cell B2 and double-clicking the 'fill handle' (little black square at the bottom right of the cell).
3. Activate AutoFilter
4. Filter column B to FALSE
5. Select all (visible) rows. This can be done quickly by selecting cell B2, hold SHIFT and double-click the bottom border of cell B2
6. Right click in the selected area and choose 'Delete Row'
7. When the deletion has happened, remove AutoFilter altogether.
8. Delete column B

Shouldn't take very long and you will be left with a list of unique values.
Upvote 0
The below code looks for duplicates in column "A" if this might help, thanks to some help last week. Hope this helps, Dan

Sub myDeleteDupRows()
Application.ScreenUpdating = False
Dim myLastRow As Long
Dim i As Long
' Capture last row
myLastRow = Range("A65536").End(xlUp).Row
' Insert temporary work column
Columns("B").Insert Shift:=xlToRight
' Insert count formula
Range("B1").Formula = "=COUNTIF(A$1:A$" & myLastRow & ",A1)"
Range("B1").AutoFill Destination:=Range("B1:B" & myLastRow)
Range("B1:B" & myLastRow).Select
' Turn formula into values
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
' Delete dup rows
For i = myLastRow To 1 Step -1
If Cells(i, 2) > 1 Then Rows(i).EntireRow.Delete
Next i
' Delete temporary work column
Columns("B").Delete Shift:=xlToLeft
Application.ScreenUpdating = True
End Sub
Upvote 0
Thanks for the plug Zack :)

If you do use my addin:

select your column that you wish to delete on (either in one sheet or multiple sheets)

Run the addin

Application Scope - Range (should be your column)
Search Option - Row search (so that it works on the entire row)
Output Choices - Delete Duplicates

and then put a tick against the "completely remove row (not just contents)" box to cull the row completely


Upvote 0
brettdj said:
Thanks for the plug Zack :)
What are you kidding Dave? That's one of the best tools ever developed for Excel. I have an obligation to plug it! :cool:
Upvote 0
I worked this one up some time back. not quite so invoved.

Public Sub Delete_duplicate_rows()
' This macro deletes rows with duplicates in the first column of a range selection.
' Or will delete all rows with duplicates in the same column as a single selected cell.
' Blanks are NOT duplicates.
' Mark Wagner, CPA 7/24/2005

Dim rowcounter As Long
Dim Cellvalue As Variant
Dim Rng As Range
Dim Target As String

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count = 1 Then


End If

Target = InputBox("This Macro will delete duplicate rows in the column you have selected." & _
Chr(13) & Chr(10) & "Enter the number of duplicate rows you want to KEEP.", _
"Delete Duplicates Macro", 1)

If Target = "" Then

GoTo EndMacro

End If

Set Rng = Selection.Rows
' ActiveSheet.UsedRange.Rows

For rowcounter = Rng.Rows.Count To 1 Step -1
Cellvalue = Rng.Cells(rowcounter, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), Cellvalue) > Target Then

End If
Next rowcounter


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back