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