Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where 1st 3 characters in string don't match criteria
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where 1st 3 characters in string don't match criteria
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where 1st 3 characters in string don't match crite
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows that do not match criteria | Excel Programming | |||
VB Delete rows that do not match criteria | Excel Programming | |||
Delete Rows in Worksheet2 that Match Criteria in Worksheet1!A1:A20 | Excel Programming | |||
Macro to delete row based on criteria (first few characters of string) | Excel Programming | |||
Delete characters from a string | Excel Programming |