Help with Macro

[Guest]

Hello,

I have a table with thousand of row of data. The column consists of part#
and operation, etc....

Within the data rows, there are multiple rows with same part#, but at a
different operations in numerical 10 to 999. I would like to create a macro
of some sort to look in all the data, sort the data by part# column, then by
operation, then find part# with multiple operations, keep the latest
operation (highest of 10-999) and delete other lower operations.

Thank for any help.

 

[Guest]

This should work. Once cells are in order, if two consecutive row have the
same part number the 1st row can be deleted because the second row will have
a higher operation number

Sub DeleteRows()

Const PartNumCol = "A"
Const OperationCol = "B"

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

Set SortRange = Range(Cells(1, 1), Cells(LastRow, LastColumn))

SortRange.Sort _
Key1:=Range(PartNumCol & 1), _
Order1:=xlAscending, _
Key2:=Range(OperationCol & 1), _
Order2:=xlAscending

RowCount = 1
Do While Not IsEmpty(Range(PartNumCol & RowCount))
If Range(PartNumCol & RowCount) = _
Range(PartNumCol & (RowCount + 1)) Then

Range(PartNumCol & RowCount).EntireRow.Delete
Else
RowCount = RowCount + 1
End If
Loop
End Sub