VBA to delete duplicate rows that have specific text on another column

[slora00]

Hello community, I hope everything is good.

I need help with the following situation. I have a file with duplicate values on column A, and a text "Keep" on column AC. I need to delete rows of duplicate values in column A that DO NOT have that "Keep" on column AC. I don't want to delete both duplicates and those rows that have "Keep", but those rows that meet both conditions.

Thank you in advance and I'll be waiting for an answer.

 

[Trebor76]

Hi slora00,

Try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()

    Dim i As Long, j As Long, k As Long
    Dim cln As New Collection
    Dim xlnCalcMethod As XlCalculation
    Dim wsSrc As Worksheet
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    Set wsSrc = ThisWorkbook.Sheets("Sheet1") 'Sheet name containing the data. Change to suit if necessary.
    j = 2 'Starting row number. Change to suit if necessaty.
    k = wsSrc.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For i = k To j Step -1
        If wsSrc.Range("AC" & i) <> "Keep" Then
            On Error Resume Next
                cln.Add CStr(wsSrc.Range("A" & i)), wsSrc.Range("A" & i)
                If Err.Number <> 0 Then
                    wsSrc.Rows(i).Delete
                    Err.Clear
                End If
            On Error GoTo 0
        End If
    Next i
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

 

[Fluff]

Duplicate to: VBA to delete duplicate and blank rows

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.