Delete Rows - adding a second condition

T

TP

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
 
B

Bob Phillips

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)
 
T

TP

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
Click to expand...
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
 
B

Bob Phillips

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)
 
M

mudraker

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
 
B

Bob Phillips

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)
 
Top