Deleting rows as if there are no qualifiers!~

[Guest]

Problem. The code below just deletes the row as if there are no qualifiers
at all.

'This deletes rows that do not belong in this data extraction. This is a
failsafe.
For i = 2 To LastRow
'NTNI
If Range("F" & i).Text = "1text" And Range("M" & i).Value >= 37.5 And
Range("N" & i).Value = "" Then _
Selection.EntireRow.Delete
'Parttime
If Range("F" & i).Text = "2 text" Or Range("F" & i).Text = _
"3text" Or Range("F" & i).Text = "4text" And _
Range("M" & i).Value >= 20 And Range("N" & i).Value = "" Then
Selection.EntireRow.Delete
'Fulltime
If Range("F" & i).Text <> "1text" Or Range("F" & i).Text <> "2text" Or _
Range("F" & i).Text <> "3text" Or Range("F" & i).Text <> _
"4text" And Range("M" & i).Value >= 40 And Range("N" & i).Value = ""
Then _
Selection.EntireRow.Delete
Next i

 

[Guest]

Your code is deleting what ever row you have selected when you enter the for
loop. You need to replace the Selection.EntireRow.Delete with

Rows(i).delete

Mike

 

[Guest]

For i = LastRow to 2 Step -1
'NTNI

If (Range("F" & i).Text = "1text" And _
Range("M" & i).Value >= 37.5 And _
Range("N" & i).Value = "") or _
((Range("F" & i).Text = "2text" Or _
Range("F" & i).Text = "3text" Or _
Range("F" & i).Text = "4text") And _
Range("M" & i).Value >= 20 And _
Range("N" & i).Value = "") or _
(Range("F" & i).Text <> "1text" And _
Range("F" & i).Text <> "2text" And _
Range("F" & i).Text <> "3text" And _
Range("F" & i).Text <> "4text" And _
Range("M" & i).Value >= 40 And _
Range("N" & i).Value = "") Then _
rows(i).Delete
end if
Next

 

[Guest]

Well... I must be a little tired. Next time I'll see if I can't come up with
a more difficult question!

 

[Guest]

That is the most obvious, but
many "unexpected rows" will pass this test I would think. the first four
conditions essentially say to ignore column F:

If Range("F" & i).Text <> "1text" Or Range("F" & i).Text <> "2text" Or _
Range("F" & i).Text <> "3text" Or Range("F" & i).Text <> _
"4text" And Range("M" & i).Value >= 40 And Range("N" & i).Value = ""
Then _

 

[Guest]

Nicole,

Tom's code uses a commonly overlooked process. When a loop is deleteing
rows/columns you almost ALWAYS want to start with the last row/column and
step backwards.

Mike

 

[Guest]

In essence there are three categories for resources, cat1 = text 1, cat2 =
text2 or text3 or text4 and cat3 will = none of the above. I didn't actually
look at your qualifier lines, but at the "rows(1).delete" which is what
helped me.

Thanks for you help as always, Tom.

 

[Guest]

You need to look at my qualifier line in my original post, your cat3 is
logically incorrect.

if I say

<> "text1" or <> "text2"

then if it is text1 it is not text2 and passes the 2nd condition
if it text2 it not text1 and passes the first condition
if it is anyting else it passes both conditions,
so using an OR connector,
everything passes that test.

Perhaps you don't notice the errors or in your test data nothing is >40 and
=""
and (cat1 or cat2)

 

[Guest]

Yet again, I must profess my adoration for Tom Ogilvy. Thank you much. It
works perfectly now.
-Nicole