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
