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.
Range("A24031").End(xlUp).Row
returns a value greater than 2? Maybe explicitly put the worksheet before that -Worksheets("compiled").Range(...)...
?