eliminating duplicates

Posted by George on December 28, 2001 6:58 AM

I have alot of duplicate invoices. I only want one line per invoice. Currently I have to sort by invoice number the delete duplicates. Is there an easier way?

Posted by Andrew Cheung on December 28, 2001 7:44 AM

Use the following Macros:
Suppose you have the Invoice Number in Column A and is sorted. And assume you have 4 Columns of Data only.

Sub Delete_Duplicate()
For r = 2 To 10000 'Assume you have 10000 data sets
If Cells(r + 1, 1) = Cells(r, 1) Then
Rows(r).ClearContents
End If
Next r
Range("A2:D1000").Sort Range("A2"), xlAscending 'Resort the Data Range from Columns A to D by Invoice Number

End Sub

Posted by Dank on December 28, 2001 7:54 AM

Here's a possible way using a small bit of code. It assumes that your invoice numbers are in column A so you'll need to change the code if that isn't the case. It uses Excel's built in filtering functionality so it runs very quickly.

Regards,
Daniel.

Sub DeleteDuplicates()
Dim rngeFilterRange As Range, lngLastRow As Long

With ActiveSheet

Set rngeFilterRange = .Range("A1:A" & .UsedRange.Rows.Count)

rngeFilterRange.Select

rngeFilterRange.AdvancedFilter xlFilterInPlace, , , True

.UsedRange.SpecialCells(xlCellTypeVisible).Copy

lngLastRow = .UsedRange.Rows.Count

.Range("A1").Offset(lngLastRow + 1, 0).PasteSpecial xlPasteAll

If .FilterMode Then .ShowAllData

.Range("A1:A" & lngLastRow + 1).EntireRow.Delete

End With

End Sub