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.
so my output will be
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.
so my output will be
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.