Deleting rows based on specific criteria (Office 97/SR2)

Topic

New Reply

I have a range of data (columns A thru I, and rows 1 to ‘varies’), with row 1 being a header row. One of the columns (column F) is location, a text field with string data. I need to cycle through the rows and delete any row that matches my criteria. In this case anytime a cell in column F begins with “PASS”, I need to delete the entire row. Here is the code I was using:

    Range("F2").Select
    rowNum = ActiveSheet.UsedRange.Rows.Count
    oRange = "F2:F" & rowNum
    Set pRange = Range(oRange)
    For Each pCell In pRange
        If Left(Trim(pCell.Value), 4) = "PASS" Then
            pCell.EntireRow.Delete
        Else
            'fall through by design
        End If
    Next

However, I just found out that if two consecutive cells in column F begin with “PASS” my code will delete the first row and skip over the second row. Any ideas on how to fix this.

Reply | Quote

Replies

No time to fix the code. Instead of the for each loop (which is normally the best) just use a for loop with a counter and start with the last row, steping by -1. HTH –Sam

Reply | Quote

I call this “The Deletion Backwards Principle” (weak pun on the Tubes 1981 album The Completion Backwards Principle). I have always been curious about this. I assume it’s because when any object in a collection is deleted, the application “resets” the component object names and pointers of the collection, but the VBA Loop can’t recognize the “reset”. Can you or anyone give me a more technical explanation of this?

Talk to Ya Later (now why would I say that?)

Reply | Quote

Your expalnation is correct, what more technical info do you need?
It is NOT a matter that the code can NOT handle it, it is that the code, as written, does NOT handle it: you would need the code to subtract the value whenever you deleted AND decrease the ending value by one (this of course is frowned upon by programmers). The excel programmers account for the deletion, the macro does not!

It is easier to code it in reverse.
Steve

Reply | Quote

I understand that it means
“For Each Object in Collection”
loops cannot be used to delete objects in a collection, the collection has to be indexed, and a
“For counter = startnum To stopnum Step -stepnum”
has to be used, and in my experience the latter takes more coding than the former.

I’d like to technically understand more about Collections than what I already posted, which I intuited.

Reply | Quote

This is NOT technical, but it is illustrative:

The for each is essentially a speciallized For x = 1 to end loop.

if you have 8 objects 1-8 in a collection. the for each logic is something like:

Go to 1 do NOT delete = no change to collection NEXT
goto 2 delete collection change! Old1 =New1, Old3 = New2, Old4=New3, etc reset end to 7! NEXT
goto 3!! OOPs bypassed OLD3 = NEW2 and instead looks at new3 = Old4. If OLD3 needed to be deleted it is ignored. The for each does NOT reset its counter it only resets the END value thus, you won’t get an error message for going past the end which you would if you used a conventional for next

Going backwards does not “jump” entries earlier in the list so they don’t get bypassed.

Steve

Reply | Quote

Well, Jan Karl wins the prize for the best solution, but I want to tackle John’s question about for-each loops and collections (where’s the teacher smilie?). Both concepts are normally wonderful: easy to use and understand. But there is a danger in the simplicity as Steve discovered. You could say that VBA should take a snapshot in time and go through each item in that collection no matter what — don’t confuse me with implementation details, just do it, which part of “each” do you not understand. But, what if an item gets deleted before the loop gets to it? Or, what if an item gets added to the collection during the for-each loop? For example, what if Steve had wanted to delete the next row after any row that had PASS in column F. Now, it gets complicated: you get different results depending on how you go through the rows. MS could have worked this out for Excel since most of it’s collections also have an order, but the concept of collections does not include order, so they had to keep the for-each loop simple. So, the bottom line is to use the for-each loop whenever possible: it is faster and the code is simpler. But if you are adding to or deleting from the collection inside the loop, then you need to stop and think what you really want to do and then implement it with a for-next loop, usually starting at the end and stepping backwards. HTH –Sam

Reply | Quote

Something like the code below should do what you want:

Public Sub DeletPASS()
Dim I As Long, lLastRow As Long
    lLastRow = Worksheets("Sheet1").Range("F65536").End(xlUp).Row - 1
    For I = lLastRow To 1 Step -1
        If Left(Worksheets("Sheet1").Range("F1").Offset(I, 0), 4) = "PASS" Then
            Worksheets("Sheet1").Range("F1").Offset(I, 0).EntireRow.Delete
        End If
    Next I
End Sub

Reply | Quote

Something like this:

Sub Macro1()
    Range("F1:F" & ActiveSheet.UsedRange.Rows.Count).AutoFilter Field:=1, _
Criteria1:="PASS"
    Rows("2:" & ActiveSheet.UsedRange.Rows.Count).Delete Shift:=xlUp
    ActiveSheet.AutoFilterMode = False
End Sub

Reply | Quote

Nice!

Reply | Quote

Thank you Jan Karel. What a simple solution.

AutoFilter didn’t occur to me as a solution. I was trying to delete rows where the value in column F started with the letters “PASS”. I didn’t realize until I was testing your solution that I could use wildcards in the criteria for AutoFilter.

I will be able to use this in multiple places. Thanks again for the time saving tip.

Reply | Quote