Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows - adding a second condition
I am using the following code, which deletes a row if a valid location is
not found in column F. Now I need to add a second criteria so that if "NE", "NW","SW", or "SE" is in column F, OR if "criteria1", "criteria2", ... "criteria10" is in column L THEN the row must be saved ELSE EntireRow.Delete. Can anyone help me nest this? Also, I have read that Case statements are faster and am wondering if I should be doing that instead. Again, I would need help with the syntax. Thanks in advance! Patti Private Sub DeleteRows() ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 If Not Cells(r, 6) = "NE" Then If Not Cells(r, 6) = "NW" Then If Not Cells(r, 6) = "SW" Then If Not Cells(r, 6) = "SE" Then Cells(r, 6).EntireRow.Delete End If End If End If End If Next r End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows - adding a second condition
Hi Patti,
Here is one way without Case Private Sub DeleteRows() Dim fDelete As Boolean Dim LstRow As Long Dim r As Long ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 fDelete = False If (Cells(r, 6) < "NE" And _ Cells(r, 6) < "NW" And _ Cells(r, 6) < "SW" And _ Cells(r, 6) < "SE") Then fDelete = True End If If fDelete Then If (Left(Cells(r, 12), 8) = "criteria" And _ Val(Mid(Cells(r, 12), 9, 99)) 0 And _ Val(Mid(Cells(r, 12), 9, 99)) < 11) Then fDelete = False End If End If If fDelete Then Cells(r, 6).EntireRow.Delete Next r End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "TP" wrote in message ... I am using the following code, which deletes a row if a valid location is not found in column F. Now I need to add a second criteria so that if "NE", "NW","SW", or "SE" is in column F, OR if "criteria1", "criteria2", ... "criteria10" is in column L THEN the row must be saved ELSE EntireRow.Delete. Can anyone help me nest this? Also, I have read that Case statements are faster and am wondering if I should be doing that instead. Again, I would need help with the syntax. Thanks in advance! Patti Private Sub DeleteRows() ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 If Not Cells(r, 6) = "NE" Then If Not Cells(r, 6) = "NW" Then If Not Cells(r, 6) = "SW" Then If Not Cells(r, 6) = "SE" Then Cells(r, 6).EntireRow.Delete End If End If End If End If Next r End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows - adding a second condition
Thanks Bob! I'll give your suggestion a try. I have to confess that you
lost me a little with the arguments "9,99" in "Val(Mid(Cells(r, 12), 9, 99)) 0 And (Mid(Cells(r, 12), 9, 99)) < 11) Then". But you have given me what I need to adapt to my situation. If anyone has any thoughts/examples on using Case statements, I'd be interested in them too! Thanks again, Patti "Bob Phillips" wrote in message ... Hi Patti, Here is one way without Case Private Sub DeleteRows() Dim fDelete As Boolean Dim LstRow As Long Dim r As Long ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 fDelete = False If (Cells(r, 6) < "NE" And _ Cells(r, 6) < "NW" And _ Cells(r, 6) < "SW" And _ Cells(r, 6) < "SE") Then fDelete = True End If If fDelete Then If (Left(Cells(r, 12), 8) = "criteria" And _ Val(Mid(Cells(r, 12), 9, 99)) 0 And _ Val(Mid(Cells(r, 12), 9, 99)) < 11) Then fDelete = False End If End If If fDelete Then Cells(r, 6).EntireRow.Delete Next r End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "TP" wrote in message ... I am using the following code, which deletes a row if a valid location is not found in column F. Now I need to add a second criteria so that if "NE", "NW","SW", or "SE" is in column F, OR if "criteria1", "criteria2", ... "criteria10" is in column L THEN the row must be saved ELSE EntireRow.Delete. Can anyone help me nest this? Also, I have read that Case statements are faster and am wondering if I should be doing that instead. Again, I would need help with the syntax. Thanks in advance! Patti Private Sub DeleteRows() ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 If Not Cells(r, 6) = "NE" Then If Not Cells(r, 6) = "NW" Then If Not Cells(r, 6) = "SW" Then If Not Cells(r, 6) = "SE" Then Cells(r, 6).EntireRow.Delete End If End If End If End If Next r End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows - adding a second condition
Patti,
A few comments for you. The technique I am using for the second set of criteria is toe test the first 8 characters for 'criteria', and the value of the rest to be 0 and < 11. This will then encompass 'criteria1 to criteria10'. The Val(Mid(Cells(r, 12), 9, 99)) statement is taking all character from the ninth onwards (as long as there are not more than 108<vbg), and testing the value of that. Not neat, but should work. As for Case. I prefer Case where it can be used as it is more structured, and more easily readable. Case works best where there is one test, and a number of possible outcomes that would be treated in different ways. Your first test, the compass points, is really a simple True/False, with 4 possible values returning the true/false, but only one action. So I would say this doesn't readily suggest Select Case, but as you will see below, it actually works better. The second criteria tests different parts of the same value, and is better served with If statements. Private Sub DeleteRows() Dim fDelete As Boolean Dim LstRow As Long Dim r As Long ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 fDelete = False Select Case Cells(r, 6) Case "NE", "NW", "SW", "SE": Case Else: fDelete = True End Select If fDelete Then If (Left(Cells(r, 12), 8) = "criteria" And _ Val(Mid(Cells(r, 12), 9, 99)) 0 And _ Val(Mid(Cells(r, 12), 9, 99)) < 11) Then fDelete = False End If End If If fDelete Then Cells(r, 6).EntireRow.Delete Next r End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "TP" wrote in message ... Thanks Bob! I'll give your suggestion a try. I have to confess that you lost me a little with the arguments "9,99" in "Val(Mid(Cells(r, 12), 9, 99)) 0 And (Mid(Cells(r, 12), 9, 99)) < 11) Then". But you have given me what I need to adapt to my situation. If anyone has any thoughts/examples on using Case statements, I'd be interested in them too! Thanks again, Patti "Bob Phillips" wrote in message ... Hi Patti, Here is one way without Case Private Sub DeleteRows() Dim fDelete As Boolean Dim LstRow As Long Dim r As Long ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 fDelete = False If (Cells(r, 6) < "NE" And _ Cells(r, 6) < "NW" And _ Cells(r, 6) < "SW" And _ Cells(r, 6) < "SE") Then fDelete = True End If If fDelete Then If (Left(Cells(r, 12), 8) = "criteria" And _ Val(Mid(Cells(r, 12), 9, 99)) 0 And _ Val(Mid(Cells(r, 12), 9, 99)) < 11) Then fDelete = False End If End If If fDelete Then Cells(r, 6).EntireRow.Delete Next r End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "TP" wrote in message ... I am using the following code, which deletes a row if a valid location is not found in column F. Now I need to add a second criteria so that if "NE", "NW","SW", or "SE" is in column F, OR if "criteria1", "criteria2", ... "criteria10" is in column L THEN the row must be saved ELSE EntireRow.Delete. Can anyone help me nest this? Also, I have read that Case statements are faster and am wondering if I should be doing that instead. Again, I would need help with the syntax. Thanks in advance! Patti Private Sub DeleteRows() ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 If Not Cells(r, 6) = "NE" Then If Not Cells(r, 6) = "NW" Then If Not Cells(r, 6) = "SW" Then If Not Cells(r, 6) = "SE" Then Cells(r, 6).EntireRow.Delete End If End If End If End If Next r End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows - adding a second condition
Bob,
I appreciate the explanation and the alternate code! Regards, Patti "Bob Phillips" wrote in message ... Patti, A few comments for you. The technique I am using for the second set of criteria is toe test the first 8 characters for 'criteria', and the value of the rest to be 0 and < 11. This will then encompass 'criteria1 to criteria10'. The Val(Mid(Cells(r, 12), 9, 99)) statement is taking all character from the ninth onwards (as long as there are not more than 108<vbg), and testing the value of that. Not neat, but should work. As for Case. I prefer Case where it can be used as it is more structured, and more easily readable. Case works best where there is one test, and a number of possible outcomes that would be treated in different ways. Your first test, the compass points, is really a simple True/False, with 4 possible values returning the true/false, but only one action. So I would say this doesn't readily suggest Select Case, but as you will see below, it actually works better. The second criteria tests different parts of the same value, and is better served with If statements. Private Sub DeleteRows() Dim fDelete As Boolean Dim LstRow As Long Dim r As Long ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 fDelete = False Select Case Cells(r, 6) Case "NE", "NW", "SW", "SE": Case Else: fDelete = True End Select If fDelete Then If (Left(Cells(r, 12), 8) = "criteria" And _ Val(Mid(Cells(r, 12), 9, 99)) 0 And _ Val(Mid(Cells(r, 12), 9, 99)) < 11) Then fDelete = False End If End If If fDelete Then Cells(r, 6).EntireRow.Delete Next r End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "TP" wrote in message ... Thanks Bob! I'll give your suggestion a try. I have to confess that you lost me a little with the arguments "9,99" in "Val(Mid(Cells(r, 12), 9, 99)) 0 And (Mid(Cells(r, 12), 9, 99)) < 11) Then". But you have given me what I need to adapt to my situation. If anyone has any thoughts/examples on using Case statements, I'd be interested in them too! Thanks again, Patti "Bob Phillips" wrote in message ... Hi Patti, Here is one way without Case Private Sub DeleteRows() Dim fDelete As Boolean Dim LstRow As Long Dim r As Long ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 fDelete = False If (Cells(r, 6) < "NE" And _ Cells(r, 6) < "NW" And _ Cells(r, 6) < "SW" And _ Cells(r, 6) < "SE") Then fDelete = True End If If fDelete Then If (Left(Cells(r, 12), 8) = "criteria" And _ Val(Mid(Cells(r, 12), 9, 99)) 0 And _ Val(Mid(Cells(r, 12), 9, 99)) < 11) Then fDelete = False End If End If If fDelete Then Cells(r, 6).EntireRow.Delete Next r End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "TP" wrote in message ... I am using the following code, which deletes a row if a valid location is not found in column F. Now I need to add a second criteria so that if "NE", "NW","SW", or "SE" is in column F, OR if "criteria1", "criteria2", .... "criteria10" is in column L THEN the row must be saved ELSE EntireRow.Delete. Can anyone help me nest this? Also, I have read that Case statements are faster and am wondering if I should be doing that instead. Again, I would need help with the syntax. Thanks in advance! Patti Private Sub DeleteRows() ' used range is base on column A ' if a row is valid, it must have a location in column F LstRow = Cells(Rows.Count, "a").End(xlUp).Row For r = LstRow To 10 Step -1 If Not Cells(r, 6) = "NE" Then If Not Cells(r, 6) = "NW" Then If Not Cells(r, 6) = "SW" Then If Not Cells(r, 6) = "SE" Then Cells(r, 6).EntireRow.Delete End If End If End If End If Next r End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows - adding a second condition
TP
I don't understanf Bob's code as to why he is using left & mid strin commands as your original post does not appear to be asking to match o part of what is in column l try this simpler code on a backup copy Code looks in column F if no match on "NE", "NW", "SW", "SE" looks at column L for "criteria1", "criteria2", "criteria3" "criteria4" if no match deletes entire row Private Sub DeleteRows() Dim lRow As Long ' used range is base on column A ' if a row is valid, it must have a location in column F For lRow = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1 Select Case Cells(lRow, "F").Value Case "NE", "NW", "SW", "SE" 'do nothing Case Else Select Case Cells(lRow, "L").Value Case "criteria1", "criteria2", "criteria3", "criteria4" 'do nothing Case Else Rows(lRow).Delete End Select End Select Next lRow End Su -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows - adding a second condition
Because it wants to test all values from criteria1 to criteria10, and I see
3 tests as simpler than 10. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "mudraker " wrote in message ... TP I don't understanf Bob's code as to why he is using left & mid string commands as your original post does not appear to be asking to match on part of what is in column l try this simpler code on a backup copy Code looks in column F if no match on "NE", "NW", "SW", "SE" looks at column L for "criteria1", "criteria2", "criteria3", "criteria4" if no match deletes entire row Private Sub DeleteRows() Dim lRow As Long ' used range is base on column A ' if a row is valid, it must have a location in column F For lRow = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1 Select Case Cells(lRow, "F").Value Case "NE", "NW", "SW", "SE" 'do nothing Case Else Select Case Cells(lRow, "L").Value Case "criteria1", "criteria2", "criteria3", "criteria4" 'do nothing Case Else Rows(lRow).Delete End Select End Select Next lRow End Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows based on condition | Excel Discussion (Misc queries) | |||
Delete rows with date more than 48 hours with a condition.. | Excel Worksheet Functions | |||
Macro to delete rows based on a condition | Excel Worksheet Functions | |||
how do I delete all rows that match a condition? | Excel Worksheet Functions | |||
delete rows with certain condition | Excel Programming |