Looks like I may not get back for a day or two, so here's same code with a
routine added at the end that will delete any hidden rows. Same deal as
before, edit as needed and decide whether or not to keep duplicates or not.

Sub MaxOperationPickerWithDelete()
'change these 5 as required
Const sheetName = "Sheet1"
Const firstDataRow = 2
Const partCol = "A"
Const opCol = "B"
Const nameCol = "C"

Dim lastRow As Long
Dim keyRow As Long
Dim currRow As LoadPictureConstants
Dim currPartNo As String
Dim currName As String
Dim currMax As Long
Dim hideLateDuplicates As Boolean

'make sure we're on the proper sheet
Worksheets(sheetName).Activate
'unhide any hidden rows from previous activity
Range(partCol & ":" & partCol).EntireRow.Hidden = False
lastRow = Range(partCol & Rows.Count).End(xlUp).Row
'initialize
keyRow = firstDataRow
Application.ScreenUpdating = False ' speed things up
Do Until keyRow = lastRow
currName = Range(nameCol & keyRow)
currPartNo = Range(partCol & keyRow)
currMax = Range(opCol & keyRow)
currRow = 1 ' use as offset
Do Until ((keyRow + currRow) = lastRow)
If Range(partCol & keyRow).Offset(currRow, 0) = _
currPartNo And _
Range(nameCol & keyRow).Offset(currRow, 0) = _
currName And _
Range(opCol & keyRow).Offset(currRow, 0) _
currMax Then
currMax = Range(opCol & keyRow).Offset(currRow, 0)
End If
currRow = currRow + 1
Loop
'we now have max operation for p#/name combination
'go back through and hide all less than max for the combo
currRow = 0 ' reset
hideLateDuplicates = False ' reset
Do Until ((keyRow + currRow) lastRow)
'don't check already hidden rows
If Range(partCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = False Then
If Range(partCol & keyRow).Offset(currRow, 0) = _
currPartNo And _
Range(nameCol & keyRow).Offset(currRow, 0) = _
currName And _
Range(opCol & keyRow).Offset(currRow, 0) <= _
currMax Then
'possible match, question is whether or not
'to hide rows with duplicates when the Op #
'matches up.
If Range(opCol & keyRow).Offset(currRow, 0) = _
currMax Then
If hideLateDuplicates Then
Range(opCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = True
Else
'leave 1st instance visible always
'delete next command to leave
'duplicates visible
hideLateDuplicates = True
End If
Else ' is less than, hide regardless
Range(opCol & keyRow).Offset(currRow, 0). _
EntireRow.Hidden = True
End If
End If
End If ' hidden row check
currRow = currRow + 1
Loop
keyRow = keyRow + 1
Do While Range(partCol & keyRow). _
EntireRow.Hidden = True And keyRow < lastRow
keyRow = keyRow + 1
Loop
Loop ' keyRow loop

'added for actual delete
'if a row is hidden, delete it
'delete from the bottom up
For keyRow = lastRow To firstDataRow Step -1
If Range(partCol & keyRow).EntireRow.Hidden = True Then
Range(partCol & keyRow).EntireRow.Delete
End If
Next

Application.ScreenUpdating = True ' display results
End Sub


"Cam" wrote:

JLatham, Here's the sample:

Befo
Part# operation STA
A 1 John
B 2 Sally
C 6 Sally
A 2 John
A 3 John
C 5 Sally

After:
Part# operation STA
A 3 John
B 2 Sally
C 6 Sally

Deleted or hide row:
A 2 John
A 3 John
C 5 Sally


"JLatham" wrote:

Can you show some sample data - doesn't have to be complete with all columns,
just some Before and After type examples - needs to have at least part #
column, operation number, and any other thing that a decision might need to
be based on.

"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.