Check for first 2 charecters in cell if not found delete the row.

[flay]

Hey.
I have this script which checks and replaces first 2 characters in a cell(total 4 strings for it check for).
I want to add that IF the cell does not start with one of these strings then it should delete the row completely.
Here is the codeThe strings im checking for are marked)
Sub Change_Replace()
Dim Ary As Variant
Dim i As Long

Ary = Array("NI", "801", "RE", "821", "NV", "571", "NF", "831")
For i = 0 To UBound(Ary) Step 2
With Range("A2", Range("A" & Rows.Count).End(xlUp))
.Value = Evaluate(Replace("if(left(@,2)=""" & Ary(i) & """," & Ary(i + 1) & "&mid(@,3,100),@)", "@", .Address))
End With
Next i
End Sub

 

[Saurabhj]

Hi,

Use below script:

Sub Change_Replace()
    Dim Ary As Variant
    Dim i As Long
   
    Ary = Array("NI", "801", "RE", "821", "NV", "571", "NF", "831")
    For i = 0 To UBound(Ary) Step 2
        For rowno = 2 To Range("A" & Rows.Count).End(xlUp).Row
            If Left(Cells(rowno, 1), Len(Ary(i))) = Ary(i) Then
                Cells(rowno, 1).Delete
            End If
        Next
    Next i
End Sub

 

[Fluff]

Another option

Sub flay()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("NI", "801", "RE", "821", "NV", "571", "NF", "831")
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if((left(@,2)<>""NI"")*(left(@,2)<>""RE"")*(left(@,2)<>""NV"")*(left(@,2)<>""NF""),"""",@)", "@", .Address))
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With

   For i = 0 To UBound(Ary) Step 2
      With Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Value = Evaluate(Replace("if(left(@,2)=""" & Ary(i) & """," & Ary(i + 1) & "&mid(@,3,100),@)", "@", .Address))
      End With
   Next i
End Sub