I have two worksheets: "compiled" and "firstpickup". What I'm looking to do is delete rows in the sheet "firstpickup" if the value in column A can also be found in column A of "compiled". "compiled" is 42,000 rows and "firstpickup" is 21,000, so I'd like to be able to just run the macro and have it end after going through all the data.

Here are some options I tried based on cursory Google searches:

 Sub RemoveDuplicates()
    Dim rCrit As Range 'Range To Search For Duplicates
    Dim rFilt As Range 'Range To Remove Duplicates
    Dim lLoop As Long

    Set rCrit = Worksheets("compiled").Range("A1", Worksheets("compiled").Range("A" & Rows.Count).End(xlUp))
    Set rFilt = Worksheets("firstpickup").Range("A1", Worksheets("firstpickup").Range("A" & Rows.Count).End(xlUp))

    For lLoop = rCrit.Rows.Count To 2 Step -1
        If WorksheetFunction.CountIf(rCrit, rFilt(lLoop).Value) > 0 Then
            Worksheets("firstpickup").Rows(lLoop).Delete shift:=xlUp
        End If
    Next lLoop
End Sub

This sub kept not finding matches in the "if" function, so it never deleted any rows, even for rows which I knew had duplicates.

Sub Removeduplicates()

Dim Row As Long
Dim FoundDup As Range


For Row = Range("A24031").End(xlUp).Row To 2 Step -1

Set FoundDup = Sheets("compiled").Range("A:A").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)

If Not FoundDup Is Nothing Then
    Cells(Row, 1).EntireRow.Delete
End If

Next Row

End Sub

This sub also wasn't successful; after the "For" query, the debugger would skip to "end sub".

Any help at all, either reworking my previous attempts or helping me with a different solution, would be greatly appreciated.

  • 1
    For your second part, are you sure Range("A24031").End(xlUp).Row returns a value greater than 2? Maybe explicitly put the worksheet before that - Worksheets("compiled").Range(...)...? – BruceWayne Dec 4 '18 at 18:55

You can use this VBA code, will help you to Delete Rows from Sheet Firstpickup, if Values in Column A will match with values in Column A of Complied Sheet.

Sub DeleRows()
Dim LR As Long, i As Long
With Sheets("Firstpickup")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If IsNumeric(Application.Match(.Range("A" & i).Value, Sheets("Complied").Columns("A"), 0)) Then .Rows(i).Delete
Next i
End With
End Sub

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.