Conditionally delete entire row if cell value in column B is not starting with word "Transfer"

[primala]

Hi,

How to conditionally delete entire row if cell value in Column B is not starting with word "Transfer", regarding uppercase, lowercase or proper. Starting from row # 2?

The number of row reaching 20,000 lines in maximum.

Thanks in advance

 

[JoeMo]

See if this works (untested) on a copy of your sheet.

Sub DeleteIfNotTransfer()
Dim lR As Long, R As Range, vA As Variant, dRws As Range, i As Long
lR = Range("B" & Rows.Count).End(xlUp).Row
Set R = Range("B2:B" & lR)
vA = R.Value
For i = LBound(vA, 1) To UBound(vA, 1)
    If Not InStr(1, UCase(vA(i, 1)), "TRANSFER") = 1 Then
        If Not dRws Is Nothing Then
            Set dRws = Union(dRws, R.Cells(i))
        Else
            Set dRws = R.Cells(i)
        End If
    End If
Next i
Application.ScreenUpdating = False
If Not dRws Is Nothing Then dRws.EntireRow.Delete
Application.ScreenUpdating = True
End Sub

 

[primala]

Dear JoeMo,

Nice solution!!! It works like a charm!

Thanks so much

 

[JoeMo]

Dear JoeMo,

Nice solution!!! It works like a charm!

Thanks so much

You are welcome - thanks for the reply.