Removing duplicates

[thomas.szwed]

Please look at the attached spreadsheet.

Here i have a demo of an extract i have taken from SAP. This demo is shortened significantly from 40,000 records to 13. My problem is that I have two records for some employees. They are different as column contains Master in one row and not in the next. If there are two of the same employee records the row i want is ALWAYS the second (the one that contains a value in column B).

Could someone help me with some code that deletes employee records that contain a blank cell in column B ONLY if there are two of the employees records???

Thanks for any help

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

.Columns(3).Insert
.Range("C1").Value = "temp"
.Range("C2").Resize(LastRow - 1).Formula = "=AND(COUNTIF(A:A,A2)>1,B2="""")"
.Columns(3).AutoFilter field:=1, Criteria1:=True
On Error Resume Next 'in case there are none
Set rng = .Range("C2").Resize(LastRow - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
.Columns(3).Delete
End With

End Sub