Conditional Row Deletion

[Guest]

I'm having trouble with this code:

Sub RowDelete()

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "D").Value <> "MN*" Or Cells(i, "D").Value <> "MP107*" Then
Rows(i).Delete
End If
Next i

End Sub

I'm trying to delete all rows where the text contained in column D DOES NOT
equal MN or MP107. When I run this procedure, it deletes ALL rows. Help!

 

[Carlos]

Hi Kirk

by your code try

Sub RowDelete()

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Left(Cells(i, "D").Value, 2) <> "MN" Or Left(Cells(i,
"D").Value, 5) <> "MP107" Then
Rows(i).Delete
End If
Next i

End Sub

 

[Nigel]

Two 'Nots' make an 'And', your conditions not equal OR not equal will
always be meet. Try using

If Cells(i, "D").Value <> "MN*" AND Cells(i, "D").Value <> "MP107*" Then

You also cannot use a wildcard in your conditions this clause eg "MN*" -
are you looking for the strings anywhere within the cell(s) ? - if so just
use MN or MP107.

 

[Guest]

Kirk,

Try

Sub RowDelete()

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Not (Cells(i, "D").Value = "MN" Or Cells(i, "D").Value = "MP107")
Then
Rows(i).Delete
End If
Next i
End Sub


You used wild cards where you specified either MN or MP107.

I also made the logic a little easier to understand.

HTH

 

[Tom Ogilvy]

You want AND instead of OR

Sub RowDelete()

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Not(Cells(i, "D").Value Like "MN*") AND _
Not(Cells(i, "D").Value Like "MP107*") Then
Rows(i).Delete
End If
Next i

End Sub


or


Sub RowDelete()

Dim i As Long
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Not(Cells(i, "D").Value Like "MN*" Or _
Cells(i, "D").Value Like "MP107*") Then
Rows(i).Delete
End If
Next i

End Sub