Delete a Duplicate Row in Excel (VBA) 1

[meierswa]

I want to be able to run a macro which searches for duplicate records in a column and, if found, deletes the entire row. I have the following code, but it only deletes the duplicate cell, not the entire row. Any help is appreciated.

Sub DeleteDuplicates()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through.
iListCount = Sheets("Sheet1").Range("A1:A100").Rows.Count
Sheets("Sheet1").Range("A1").Select
' Loop until end of records.
Do Until ActiveCell = ""
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the column number.
If ActiveCell.Row <> Sheets("Sheet1").Cells(iCtr, 1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "You are the MAN!"
End Sub

 

[AnotherHiggins]

Without reading your code...if it does what you want except for not deleting the entire row, then just add this:

Sheets("Sheet1").Cells(iCtr, 1).entirerow.Delete 'xlShiftUp This is no longer needed


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886