Deleting Duplicate Values

[Tech Teacher]

I am attempting to delete duplicate rows where only one
value in a column is a duplicate. I found the FindDups
macro in the KB, but I can't get it to work. Here is
what I'm trying to do in Excel 2000 on XP

Becky 3
Adam 3
Kyle 2
Susan 1

I want either the entire row of Adam or Becky to be
deleted and only unique values in column 2 to remain.
Can anyone suggest a function or macro to accomplish this?

Thanks
Ohio Tech Teacher

 

[Andy B]

Hi

Have a look here. Chip has loads of stuff on duplicates.
http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

 

[Frank Kabel]

Hi
try the following macro (see:
http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows)
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
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 2).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(2), V) > 1
Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Tushar Mehta]

If this is something you do on an infrequent basis, just do it through
the GUI. Sort the data on the 2nd column. Duplicate entries will now
be adjacent to one another and easily deleted.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Business solutions leveraging technology
Microsoft Most Valuable Professional (MVP) 2000-2004

 

[Tushar Mehta]

Check Chip Pearson's
http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Business solutions leveraging technology
Microsoft Most Valuable Professional (MVP) 2000-2004