Deleting Duplicates based on Active condition

[mjh4ck3r]

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.php?image=https%3A%2F%2Fi.stack.imgur.com%2F2cZZu.png&hash=db12f5ec6314a4854e3b903756241db2 (https://i.stack.imgur.com/2cZZu.png)

so my output will be

https://www.mrexcel.com/board/proxy.php?image=https%3A%2F%2Fi.stack.imgur.com%2FCtbQB.png&hash=5a9b5faf747f446c2f80474626ac2596 (https://i.stack.imgur.com/CtbQB.png)

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.

[Fluff]

Cross posted https://www.mrexcel.com/board/threads/deleting-duplicates-based-on-active-condition.1135918/