Evaluate a list of values in a column against a combobox value most efficiently

Question

I am trying to delete duplicate values in a temporary list based on a value in a combobox. The code below loops through individual rows to check whether a value matches. It is slow.

Dim ws As Worksheet
Dim i As Long

Set ws = Sheets("TempList3")

On Error Resume Next

For i = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
    If Cells(i, 2) <> Sheets("Sheet1").ComboBox2.Value Then
        ws.Rows(i).EntireRow.Delete
    End If
Next

Is there a way to evaluate the entire column's values against the combobox's value once and then delete all rows on a worksheet. Or perhaps there is a better way?

Answer 1

I used a looping Find function, it deletes the row where the value was found and then it searches again and deletes the next row it finds until it can no longer find the Combo value on the sheet:

Sub find_cell()
Dim find_cell As Range

Set ws = Sheets("TempList3")

stop_loop = False

Do Until stop_loop = True
    Set find_cell = ws.Cells.Find(What:=Sheets("Sheet1").ComboBox2.Value, LookAt:=xlWhole)
        If Not find_cell Is Nothing Then
           ws.Rows(find_cell.Row).EntireRow.Delete
        Else
           stop_loop = True
        End If
Loop
End Sub