Deleting rows as if there are no qualifiers!~

[Nicole Seibert]

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

[crazybass2]

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


"Nicole Seibert" wrote:

> 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

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"Nicole Seibert" wrote:

> 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

[Nicole Seibert]

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

"crazybass2" wrote:

> 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
>
>
> "Nicole Seibert" wrote:
>
> > 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

[Tom Ogilvy]

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 _

--
Regards,
Tom Ogivly



"crazybass2" wrote:

> 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
>
>
> "Nicole Seibert" wrote:
>
> > 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

[crazybass2]

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

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Nicole Seibert" wrote:
>
> > 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

[Nicole Seibert]

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.
"Tom Ogilvy" wrote:

> 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 _
>
> --
> Regards,
> Tom Ogivly
>
>
>
> "crazybass2" wrote:
>
> > 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
> >
> >
> > "Nicole Seibert" wrote:
> >
> > > 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

[Tom Ogilvy]

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)
--
Regards,
Tom Ogilvy


"Nicole Seibert" wrote:

> 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.
> "Tom Ogilvy" wrote:
>
> > 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 _
> >
> > --
> > Regards,
> > Tom Ogivly
> >
> >
> >
> > "crazybass2" wrote:
> >
> > > 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
> > >
> > >
> > > "Nicole Seibert" wrote:
> > >
> > > > 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

[Nicole Seibert]

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

"Tom Ogilvy" wrote:

> 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)
> --
> Regards,
> Tom Ogilvy
>
>
> "Nicole Seibert" wrote:
>
> > 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.
> > "Tom Ogilvy" wrote:
> >
> > > 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 _
> > >
> > > --
> > > Regards,
> > > Tom Ogivly
> > >
> > >
> > >
> > > "crazybass2" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Nicole Seibert" wrote:
> > > >
> > > > > 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