General discussion


delete the row with duplicate item

By Hunterzh ·
How to find duplicate items and delete one (row)?

In Excel 2000, we have one column with many items, some are duplicate. Now I can find duplicate items by using COUNTIF function. But there are so many items, over two thousand, with hundreds of duplicate items. I hope to delete one row (with larger address number) which has duplicated item.

How can I delete duplicate items quickly? By using a function, macro?
For example, cell B18 and B6 have duplicate items, now I want to delete row 18.

Thanks for the answer from DKlippert, but I need to delete the complete row which has the duplicate item. Only filtering cannot solve this problem.

Use Data>Filter Advanced filter
Select the column
Choose the Copy to another location option
Enter the first cell of a target range
Put a check mark in Unique records only


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by abailey101 In reply to delete the row with dupli ...

Found on website:

DeleteDuplicateRowsThis macro will delete duplicate rows in a range. To use, select a single-column range of cells, comprising the range of rows from which duplicates are to be deleted, e.g., C2:C99. To determine whether a row has duplicates, the values in the selected column are compared. Entire rows are not compared against one another. Only the selected column is used for comparison. When duplicate values are found in the active column, the first row remains, and all subsequent rows are deleted. Public Sub DeleteDuplicateRows()'' This macro deletes duplicate rows in the selection. Duplicates are' counted in the COLUMN of the active cell.
Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Col = ActiveCell.Column
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
N = N + 1 End If
Next r
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Collapse -

by Hunterzh In reply to

Poster rated this answer.

Collapse -

by Hunterzh In reply to delete the row with dupli ...

This question was closed by the author

Related Discussions

Related Forums