Consulting

Results 1 to 2 of 2

Thread: Deleting Duplicates based on Active condition

  1. #1
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    4
    Location

    Exclamation 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.



    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.

  2. #2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •