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.

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



"Cam" wrote:

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.