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

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


"miek" <(E-Mail Removed)> wrote in message
news:5935370C-2402-4B6D-B385-(E-Mail Removed)...
> 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

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

"miek" wrote:

> 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

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

"Bob Umlas" wrote:

> 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
>
>
> "miek" <(E-Mail Removed)> wrote in message
> news:5935370C-2402-4B6D-B385-(E-Mail Removed)...
> > 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
>
>
>