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
|