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!!

Hi,

Right click your sheet tab, view code and paste this in and run it. Note
I've hard-coded the criteria but this could come from a cell

Sub Marine()
Dim Criteria As String
Criteria = "A45" 'Change to suit
mycolumn = "E" 'Change to suit
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, mycolumn).End(xlUp).Row
Set MyRange = Range(mycolumn & "1:" & mycolumn & LastRow)
For Each C In MyRange
If InStr(1, C.Value, Criteria, 1) <> 1 Then
If MyRange1 Is Nothing Then
Set MyRange1 = C.EntireRow
Else
Set MyRange1 = Union(MyRange1, C.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

Mike

"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!!

Hi

Try this:

kpxRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For kpxTemp = kpxRow To 1 Step -1
CellVal = Left(Range("F" & kpxTemp).Value, 3)
If CellVal <> "A45" And CellVal <> "123" Then
Rows(kpxTemp).Delete
End If
Next

Hopes this heps.

---
Per

"Peruanos72" <(E-Mail Removed)> skrev i meddelelsen
news:A1F26184-ECAB-4F41-81EF-(E-Mail Removed)...
> 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!!

Give this code a try...

Sub DeleteRows()
Dim kpxRow As Long
Dim kpxTemp As Long
Const Criteria As String = "A45,B56,987"
With ActiveSheet
kpxRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For kpxTemp = kpxRow To 1 Step -1
If InStr("," & Criteria & ",", "," & Left(.Cells(kpxTemp, _
"F").Value, 3) & ",") Then .Rows(kpxTemp).Delete
Next
End With
End Sub

Change the assignment to the Criteria constant (the statement that starts
with Const) to show a comma separated list of all criteria you want to use
decide which rows to delete (make sure you do *not* add any spaces around
the commas to "neaten" things up).

--
Rick (MVP - Excel)


"Peruanos72" <(E-Mail Removed)> wrote in message
news:A1F26184-ECAB-4F41-81EF-(E-Mail Removed)...
> 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!!

Hey Mike,

The code works great however it doesn't seem to work when the criteria is
all numbers. Also, is there a way to add more than one criteria?
Ex: "A45" and "987" ect...

Thanks again.

"Mike H" wrote:

> Hi,
>
> Right click your sheet tab, view code and paste this in and run it. Note
> I've hard-coded the criteria but this could come from a cell
>
> Sub Marine()
> Dim Criteria As String
> Criteria = "A45" 'Change to suit
> mycolumn = "E" 'Change to suit
> Dim MyRange, MyRange1 As Range
> LastRow = Cells(Rows.Count, mycolumn).End(xlUp).Row
> Set MyRange = Range(mycolumn & "1:" & mycolumn & LastRow)
> For Each C In MyRange
> If InStr(1, C.Value, Criteria, 1) <> 1 Then
> If MyRange1 Is Nothing Then
> Set MyRange1 = C.EntireRow
> Else
> Set MyRange1 = Union(MyRange1, C.EntireRow)
> End If
> End If
> Next
> If Not MyRange1 Is Nothing Then
> MyRange1.Delete
> End If
> End Sub
>
> Mike
>
> "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!!

It worked. Thanks!!

"Rick Rothstein" wrote:

> Give this code a try...
>
> Sub DeleteRows()
> Dim kpxRow As Long
> Dim kpxTemp As Long
> Const Criteria As String = "A45,B56,987"
> With ActiveSheet
> kpxRow = .Cells(.Rows.Count, "F").End(xlUp).Row
> For kpxTemp = kpxRow To 1 Step -1
> If InStr("," & Criteria & ",", "," & Left(.Cells(kpxTemp, _
> "F").Value, 3) & ",") Then .Rows(kpxTemp).Delete
> Next
> End With
> End Sub
>
> Change the assignment to the Criteria constant (the statement that starts
> with Const) to show a comma separated list of all criteria you want to use
> decide which rows to delete (make sure you do *not* add any spaces around
> the commas to "neaten" things up).
>
> --
> Rick (MVP - Excel)
>
>
> "Peruanos72" <(E-Mail Removed)> wrote in message
> news:A1F26184-ECAB-4F41-81EF-(E-Mail Removed)...
> > 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!!
>
>