Multiple IF NOT OR

[Robert]

The following deletes rows where there is no matching text in column B

Dim RowNdx As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 5 Step -1
If Not (Cells(RowNdx, "B").Value = "EB1" Or _
Cells(RowNdx, "B").Value = "EB10" Or _
Cells(RowNdx, "B").Value = "EB11" Or _
Cells(RowNdx, "B").Value = "EB2" Or _
Cells(RowNdx, "B").Value = "EB3" Or _
Cells(RowNdx, "B").Value = "EB8" Or _
Cells(RowNdx, "B").Value = "EB9" Or _
Cells(RowNdx, "B").Value = "EE3" Or _
Cells(RowNdx, "B").Value = "EE6" Or _
Cells(RowNdx, "B").Value = "EF2" Or _
Cells(RowNdx, "B").Value = "EF3" Or _
Cells(RowNdx, "B").Value = "EG2" Or _
Cells(RowNdx, "B").Value = "WR") Then
Rows(RowNdx).Delete
End If
Next RowNdx

Unfortunately I now need to expand the number of criteria considerably
and have come up against the limit.

Grateful for suggestions as to how I can get around this.

 

[Don Guillett]

try it this way
Sub selectchoices()
Dim i As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = LastRow To 5 Step -1
Select Case UCase(Cells(i, "b"))
Case Is = "EB10", "EB11", _
"EG2" 'etc

ans = "no"
Case Else
ans = "yes"
End Select
If ans = "yes" Then Rows(i).Delete
Next
End Sub

 

[Mike H]

One way

Put your criteria in a column, in this case column A on sheet 2 and then use
this code

Sub quickrows()
LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set myrange1 = Sheets("Sheet2").Range("A1:A" & LastRow)
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 5 Step -1
For Each c In myrange1
If Cells(RowNdx, "B").Value = c.Value Then
Rows(RowNdx).Delete
End If
Next
Next RowNdx
End Sub

Mike

 

[Dave Peterson]

Dim myList as variant
dim res as variant
mylist = array("EB1","EB10","EB11") 'I'm too lazy!

res = application.match(cells(rowndx,"B").value, mylist,0)

if iserror(res) then
'not in the list, delete it
else
'it's in the list
end if

Application.match() is not case sensitive, though.

 

[Robert]

Many thanks for the responses