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 Sheets("compiled").Select 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.