VBA to delete specific duplicate in column A based on column C

[sunwave]

I have two columns A and C. The column A has names, and column C has values 'Current' and 'Expired'.

There are duplicate names in column A and I want to remove the specific duplicate (entire row) that have value ‘Expired’ in column C.

Because the file is very large the formatting and filtering doesn’t work.

I want to run VBA to do that. What would be the code to achieve this?

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[sunwave]

I have two columns A and C. The column A has names, and column C has values 'Current' and 'Expired'.

There are duplicate names in column A and I want to remove the specific duplicate (entire row) that have value ‘Expired’ in column C.

Because the file is very large the formatting and filtering doesn’t work.

I want to run VBA to do that. What would be the code to achieve this?

 

[sunwave]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[sunwave]

From the screenshot posted, I want to delete the duplicate "Lauren" (column A) that is "expired" (column C) .
I want to delete the entire row instead of just the cell.
Thanks

 

[mumps]

Try:

Sub DeleteDups()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, dic As Object
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A2:A" & lRow).Resize(, 3).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = UBound(v) To LBound(v) Step -1
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            If WorksheetFunction.CountIf(Range("A:A"), v(i, 1)) > 1 And v(i, 3) = "Expired" Then
                Rows(i + 1).Delete
            End If
        Else
            If WorksheetFunction.CountIf(Range("A:A"), v(i, 1)) > 1 And v(i, 3) = "Expired" Then
                Rows(i + 1).Delete
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[sunwave]

Try:

Sub DeleteDups()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, dic As Object
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A2:A" & lRow).Resize(, 3).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = UBound(v) To LBound(v) Step -1
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            If WorksheetFunction.CountIf(Range("A:A"), v(i, 1)) > 1 And v(i, 3) = "Expired" Then
                Rows(i + 1).Delete
            End If
        Else
            If WorksheetFunction.CountIf(Range("A:A"), v(i, 1)) > 1 And v(i, 3) = "Expired" Then
                Rows(i + 1).Delete
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

This worked liked magic! Thank you!!!

 

[mumps]

You are very welcome.