| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows as if there are no qualifiers!~
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting rows as if there are no qualifiers!~
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting rows as if there are no qualifiers!~
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting rows as if there are no qualifiers!~
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting rows as if there are no qualifiers!~
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting rows as if there are no qualifiers!~
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting rows as if there are no qualifiers!~
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting rows as if there are no qualifiers!~
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting rows as if there are no qualifiers!~
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
| Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
| Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
| Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming | |||
| deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions | |||
Linear Mode
