Excel - Identify and delete duplicate rows

[Guest]

I have a MS Excel 2003 sheet with with rows of information about staff, first
name, surname and staff, staff no.

The spreadsheet is very big how can I identify any duplicate row enties and
delete the duplicate for the whole speadsheet by using a macro or other
method.

A duplicate row will be where the first name, surname and staff no. match
another in the spreadsheet and where one entry needs to be deleted.

 

[Bernard Liengme]

Chip as lots about duplicates at www.cpearson.com
best wishes

 

[Guest]

Adjust the cell references to suit your needs.

With Cells
Set rng = .Range(.Cells(1, 8), .Cells(1, 8).End(xlDown))
rng.Select
End With


Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value And _
Cells(RowNdx, ColNum - 2) = Cells(RowNdx - 1, ColNum - 2).Value Then
Cells(RowNdx - 1, ColNum - 1) = Cells(RowNdx - 1, ColNum - 1) +
Cells(RowNdx, ColNum - 1)
Cells.EntireRow(RowNdx).Delete shift:=xlUp
End If
Next RowNdx