Hello all,

I have id numbers in column "E" (alphanumeric) and I want to find the cells
in column "E" where the first three characters of the id number DO NOT match
my criteria and then delete that entire row. Ex: If my criteria is "A45" and
the id numbers are "A454K90HR37" and "B47H33GT08", then I want to delete the
row containing the id number "B47H33GT08". I may have more than one set of
criteria. Ex: "A45", "B56", "987", etc... that I want to keep.

Note: I'm currently using the following code to find those cells in column
"E" where if the length of the id number is less than 16 characters the row
is deleted. Don't know if this code can be modifed to do both or not.

kpxRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For kpxTemp = kpxRow To 1 Step -1
If Len(Trim(Range("E" & kpxTemp))) < 16 Then
Rows(kpxTemp).Delete
End If
Next

How can I do this?

Thanks in advance!!

you're almost there

> kpxRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
> For kpxTemp = kpxRow To 1 Step -1
If LEFT(Trim(Range("E" & kpxTemp),3) <> "A45" Then
Rows(kpxTemp).Delete
End If
> Next


"Peruanos72" wrote:

> Hello all,
>
> I have id numbers in column "E" (alphanumeric) and I want to find the cells
> in column "E" where the first three characters of the id number DO NOT match
> my criteria and then delete that entire row. Ex: If my criteria is "A45" and
> the id numbers are "A454K90HR37" and "B47H33GT08", then I want to delete the
> row containing the id number "B47H33GT08". I may have more than one set of
> criteria. Ex: "A45", "B56", "987", etc... that I want to keep.
>
> Note: I'm currently using the following code to find those cells in column
> "E" where if the length of the id number is less than 16 characters the row
> is deleted. Don't know if this code can be modifed to do both or not.
>
> kpxRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
> For kpxTemp = kpxRow To 1 Step -1
> If Len(Trim(Range("E" & kpxTemp))) < 16 Then
> Rows(kpxTemp).Delete
> End If
> Next
>
> How can I do this?
>
> Thanks in advance!!

i'm getting a syntax error on line 3. Thoughts?

And if I want to add more criteria would I seperate them
by a comma "," or something else?

Ex: ... <> "A45", "B90", "767

possible?

"Patrick Molloy" wrote:

> you're almost there
>
> > kpxRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
> > For kpxTemp = kpxRow To 1 Step -1
> If LEFT(Trim(Range("E" & kpxTemp),3) <> "A45" Then
> Rows(kpxTemp).Delete
> End If
> > Next
>
>
> "Peruanos72" wrote:
>
> > Hello all,
> >
> > I have id numbers in column "E" (alphanumeric) and I want to find the cells
> > in column "E" where the first three characters of the id number DO NOT match
> > my criteria and then delete that entire row. Ex: If my criteria is "A45" and
> > the id numbers are "A454K90HR37" and "B47H33GT08", then I want to delete the
> > row containing the id number "B47H33GT08". I may have more than one set of
> > criteria. Ex: "A45", "B56", "987", etc... that I want to keep.
> >
> > Note: I'm currently using the following code to find those cells in column
> > "E" where if the length of the id number is less than 16 characters the row
> > is deleted. Don't know if this code can be modifed to do both or not.
> >
> > kpxRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
> > For kpxTemp = kpxRow To 1 Step -1
> > If Len(Trim(Range("E" & kpxTemp))) < 16 Then
> > Rows(kpxTemp).Delete
> > End If
> > Next
> >
> > How can I do this?
> >
> > Thanks in advance!!