Good morning

I have the following code to delete all rows that do not contain the
following two variables, in column P, "cw" and "ea".

Set ws = ActiveSheet

For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
If ws.Cells(p, 16) <> "cw" Then
If ws.Cells(p, 16) <> "ea" Then
ws.Rows(p).Delete
End If
End If
Next

It works very well apart from the fact that it deletes the Header row.
Please can someone assist me in modifying this code so it does not delete Row
1 (the header row).

Thanks for any help in advance, I really appreciate it.

Try changing your loop control to :

For p = ws.Range("P65536").End(xlUp).Row To 2 Step -1

Regards

Kevin


"PVANS" <(E-Mail Removed)> wrote in message
news:692E1DCE-3A31-4698-850E-(E-Mail Removed)...
> Good morning
>
> I have the following code to delete all rows that do not contain the
> following two variables, in column P, "cw" and "ea".
>
> Set ws = ActiveSheet
>
> For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
> If ws.Cells(p, 16) <> "cw" Then
> If ws.Cells(p, 16) <> "ea" Then
> ws.Rows(p).Delete
> End If
> End If
> Next
>
> It works very well apart from the fact that it deletes the Header row.
> Please can someone assist me in modifying this code so it does not delete
> Row
> 1 (the header row).
>
> Thanks for any help in advance, I really appreciate it.
>

aha! managed to fix it, I simply changed:
For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1

to:
For p = ws.Range("P65536").End(xlUp).Row To 2 Step -1

Have tested that it still works correctly, by placing a row in row to that
is not equal to "cw" and it was correctly deleted.

Thanks to any and everyone who took a look at this for me

"PVANS" wrote:

> Good morning
>
> I have the following code to delete all rows that do not contain the
> following two variables, in column P, "cw" and "ea".
>
> Set ws = ActiveSheet
>
> For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
> If ws.Cells(p, 16) <> "cw" Then
> If ws.Cells(p, 16) <> "ea" Then
> ws.Rows(p).Delete
> End If
> End If
> Next
>
> It works very well apart from the fact that it deletes the Header row.
> Please can someone assist me in modifying this code so it does not delete Row
> 1 (the header row).
>
> Thanks for any help in advance, I really appreciate it.
>

Hi Kevin,

Thanks for the reply - I had literally just tried that and it works
perfectly. Thanks for the help. I do appreciate it

Have a good day

Paul

"kc-mass" wrote:

> Try changing your loop control to :
>
> For p = ws.Range("P65536").End(xlUp).Row To 2 Step -1
>
> Regards
>
> Kevin
>
>
> "PVANS" <(E-Mail Removed)> wrote in message
> news:692E1DCE-3A31-4698-850E-(E-Mail Removed)...
> > Good morning
> >
> > I have the following code to delete all rows that do not contain the
> > following two variables, in column P, "cw" and "ea".
> >
> > Set ws = ActiveSheet
> >
> > For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
> > If ws.Cells(p, 16) <> "cw" Then
> > If ws.Cells(p, 16) <> "ea" Then
> > ws.Rows(p).Delete
> > End If
> > End If
> > Next
> >
> > It works very well apart from the fact that it deletes the Header row.
> > Please can someone assist me in modifying this code so it does not delete
> > Row
> > 1 (the header row).
> >
> > Thanks for any help in advance, I really appreciate it.
> >
>
>
> .
>