Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:
    <pre> 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
    </pre>


    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.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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?) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Something like the code below should do what you want:

    <pre>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
    </pre>

    Legare Coleman

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Something like this:

    <pre>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
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: The Completion Backwards Principle

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    Nice! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •