I think the code below will do it for you. Put it into a regular code
module: [Alt]+[F11] to open the VB Editor, Insert | Module from the VBE menu
and copy, paste and edit the code as required. Close VBE and have at it.
The Const values must be changed to match proper sheet name, first row with
data to check, and columns with the 3 items of data in them.
There's also one line of code that will give you ability to decide whether
or not to hide duplicate entries for a max value. Right now it's set to hide
duplicates. by deleting the line of code that says "hideLateDuplicates =
True" duplicates will be left visible. If you leave it in the code, then the
first value with match of highest Op# value will be left visible and all
others will be hidden.
Now, if you'll run this and let me know if it is doing what you need, and
let me know what to do with duplicates, then I'll set it up to actually
delete the rows rather than simply hide them.
Sub MaxOperationPicker()
'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 use
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
Application.ScreenUpdating = True ' display results
End Sub
