Deleting Duplicates based on Active condition

I'm new to VBA scripting trying to write a condition for the table, The condition is to take the fail status and loop through the same table and find if the appropriate ID has a pass status, if yes i need to delete the record, if no i have to retain the fail status.

https://www.mrexcel.com/board/proxy....3b903756241db2

so my output will be

https://www.mrexcel.com/board/proxy....80474626ac2596

i tried modifying code from internet when i tried running it my entire cell gets deleted.
Please help

Sub MyMacro()
Dim lastRow As Long
Dim myRow As Long

Application.ScreenUpdating = False

Range("A1").CurrentRegion.Sort _
key1:=Range("A1"), order1:=xlAscending, _
key2:=Range("B1"), order2:=xlAscending, Header:=xlYes

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For myRow = lastRow To 2 Step -1
If Cells(myRow, "A") = Cells(myRow - 1, "A") And _
UCase(Cells(myRow, "B")) = "FAIL" And _
UCase(Cells(myRow - 1, "B")) = "PASS" Then
Rows(myRow).Delete
End If
Next myRow

Application.ScreenUpdating = True
End Sub

i know who to do the same in SQL by doing a self join where the second join to have filter of status Fail with status pass with first table.

Cross posted https://www.mrexcel.com/board/thread...ition.1135918/