Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alphanumeric Sorting - numeric alpha numeric | Excel Worksheet Functions | |||
VLOOKUP with numeric and alphanumeric values | Excel Discussion (Misc queries) | |||
Converting Alphanumeric numbers to Numeric | Excel Worksheet Functions | |||
How to change alphanumeric to numeric | Excel Programming | |||
VBA - Looking to strip alphanumeric from numeric | Excel Programming |