Conditional Row Deletion
 

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!

Conditional Row Deletion
 

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

"Kirk P." wrote in message
...
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!

Conditional Row Deletion
 

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.


--
Cheers
Nigel



"Kirk P." wrote in message
...
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!

Conditional Row Deletion
 

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

Conditional Row Deletion
 

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


--
Regards,
Tom Ogilvy


"Kirk P." wrote in message
...
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!