Deleteing duplicate info
Posted by grahamcave on October 10, 2000 8:36 AM
I am working on the year end Production Schedule. I must have this report ready for the yearly Salesman meeting in mid November. Each day our company produces a status report that tracks every order in house. This is between 3500 to 4000 lines of info (columns A-Q) each day in an Excel format. If an order remains in the same status for more than one day, the next days' report will have the same information (exactly) for that particular order. I have complied each days status report from Jan 01 to Sept 25 into one workbook. There are many duplicate entries. This file is huge, and I need to know how I can delete or hide this duplicate information so that nothing is repeated. Any help is GREATLY appreciated.
Posted by Jim Knicely on October 10, 2000 10:52 AM
Wow ... that's a big workbook. Is there one column that uniquely identifies each row? It's be easier to search for dups on one columun than on coumns A-Q ?
Posted by grahamcave on October 10, 2000 12:04 PM
No, column that uniquely identifies each row.
bummer,
Posted by Celia on October 10, 2000 6:32 PM
Graham
Before running the macro you need to sort your data first, since the macro only deletes a row if it is a duplicate of the previous row.
Sub Delete_Duplicate_Rows()
Dim cols As Range
Dim firstR%, lastR%, R%
Dim firstC%, lastC%, C%
Dim Dup As Boolean
Set cols = Range("A:Q")
With Intersect(cols, ActiveSheet.UsedRange)
firstR = .Cells(1).Row + 1
lastR = .Cells(.Cells.Count).Row
firstC = .Cells(1).Column
lastC = .Cells(.Columns.Count).Column
End With
For R = lastR To firstR Step -1
Dup = True
For C = firstC To lastC
If Cells(R, C).Value <> Cells(R - 1, C).Value Then
Dup = False
Exit For
End If
Next
If Dup Then Rows(R).Delete
Next
End Sub
