Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where 1st 3 characters in a cell DO NOT meet 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 a cell DO NOT meet criteria
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where 1st 3 characters in a cell DO NOT meet criteria
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" skrev i meddelelsen ... 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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where 1st 3 characters in a cell DO NOT meet criteria
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" wrote in message ... 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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where 1st 3 characters in a cell DO NOT meet crite
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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where 1st 3 characters in a cell DO NOT meet crite
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" wrote in message ... 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 | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Delete rows that do not meet specific criteria | Excel Programming | |||
Macro, delete rows that meet criteria | Excel Programming | |||
Delete Rows where cells does not meet criteria | Excel Worksheet Functions | |||
how do i delete rows when cells meet certain criteria? | Excel Programming |