Q:

Search and delete vba code needs optimizing

I have an excel workbook with 2 sheets (ProductList and CurrentProducts)
I have the following code :
Sub Macro1()

Dim Lastrow As Integer
Dim x As Integer
Dim BinNo As String
Dim MyCell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Lastrow = Sheets("ProductsList").Range("A65536").End(xlUp).Row

For x = Lastrow To 2 Step -1

BinNo = Sheets("ProductsList").Range("A" & x).Value

With Sheets("CurrentProducts").Range("A:A")
    Set MyCell = .Find(What:=BinNo, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)

    If Not MyCell Is Nothing Then
        Sheets("CurrentProducts").Range(MyCell.Address).EntireRow.Delete
    End If
 End With

Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

What this does is take each value from column A in ProductList, searches for it in CurrentProducts and if it finds the value then deletes the entire row from CurrentProducts so I am left with any new products in the CurrentProducts sheet.
This code works but is very slow and takes about 5 minutes to run.
Each sheet has about 30,000 rows.
Is there a way to speed this up or is it just because there are so many rows?

A:

I would suggest that this can be done a lot quicker through the use of a formula. For instance you could do a vlookup. You could then sort the sheet and delete any rows that returned a value.
This is one possible solution.
There are many similar things I can think of. But using a formula would be the easiest.

