mjh4ck3r
06-02-2020, 01:08 AM
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.
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.