Delete rows where 1st 3 characters in a cell DO NOT meet criteria

[Peruanos72]

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

 

[Mike H]

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

 

[Per Jessen]

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.

 

[Rick Rothstein]

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).

 

[Peruanos72]

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.

 

[Peruanos72]

It worked. Thanks!!

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).