Delete worksheet row based on condition

[Guest]

Could someone help get me started on with the following:

I have text in column B that has a "P", "S", or "".
Number of rows varies, but ends with an empty row.

How do I step through each row and test if column B
has a value <> "P", delete that row?

Thanks for any help

 

[Bob Umlas]

Sub DeleteNotP()
'do it backwards so deleting rows doesn't mess this up!
for i=range("B65536").end(xlup).Row to 1 step -1
if cells(i,2).value <> "P" then rows(i).Delete
Next
End Sub
Bob Umlas
Excel MVP

 

[Guest]

The delay function should be put at the top of the module. It allows time
for the delete action to complete before going to the next cell. Otherwise,
some of the rows might not delete.

Public Function HalfSecDly()
s = Timer + 0.1
Do While Timer < s
DoEvents
Loop
End Function

Sub delRw()
lr = Cells(Rows.Count, 2).End(xlUP).Row
For each c in Range("B2:B" & lr)
If c <> "P" Then
c.EntireRow.Delete
HalfSecDly
End If
Next
End Sub

 

[Guest]

You're right Bob. I'm slipping into senility here.