Using alphanumeric and numeric criteria in same code
 

I was given the following code and it works but I need to also add criteria
that is numeric only. When I add numeric only criteria it does not work.
Thoughts??

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

Using alphanumeric and numeric criteria in same code
 

if you use numbers, then does your function LEFT(....,3) need to be changed
to ,2) ?


"Peruanos72" wrote:

I was given the following code and it works but I need to also add criteria
that is numeric only. When I add numeric only criteria it does not work.
Thoughts??

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

Using alphanumeric and numeric criteria in same code
 

It works for numbers like 123 and 456 (I just tried it), so you will need to
show us the Const statement you are using (so we can see how you are
specifying your numbers) and tell us how the number is formatted (if at all)
in the cells of Column E.

--
Rick (MVP - Excel)


"Peruanos72" wrote in message
...
I was given the following code and it works but I need to also add criteria
that is numeric only. When I add numeric only criteria it does not work.
Thoughts??

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

Using alphanumeric and numeric criteria in same code
 

When the OP asked this question originally, he specified he needed to work
with the first 3 characters and posted this as an example...

Ex: "A45" and "987" ect...

--
Rick (MVP - Excel)


"Patrick Molloy" wrote in message
...
if you use numbers, then does your function LEFT(....,3) need to be
changed
to ,2) ?


"Peruanos72" wrote:

I was given the following code and it works but I need to also add
criteria
that is numeric only. When I add numeric only criteria it does not work.
Thoughts??

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

Using alphanumeric and numeric criteria in same code
 

It works now. it was the formatting and how I was importing the file into
excel.

Thanks!!

"Rick Rothstein" wrote:

It works for numbers like 123 and 456 (I just tried it), so you will need to
show us the Const statement you are using (so we can see how you are
specifying your numbers) and tell us how the number is formatted (if at all)
in the cells of Column E.

--
Rick (MVP - Excel)


"Peruanos72" wrote in message
...
I was given the following code and it works but I need to also add criteria
that is numeric only. When I add numeric only criteria it does not work.
Thoughts??

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

Using alphanumeric and numeric criteria in same code
 

i'm not sure. Not to experienced with code but i'm learning. The formatting
of the
cells did the trick though. Thx anyway.

"Patrick Molloy" wrote:

if you use numbers, then does your function LEFT(....,3) need to be changed
to ,2) ?


"Peruanos72" wrote:

I was given the following code and it works but I need to also add criteria
that is numeric only. When I add numeric only criteria it does not work.
Thoughts??

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