none
Delete a row from within for each statement RRS feed

  • Question

  • Hi folks,

    I've got the following code

    For Each cell In rng.Cells
           cell.Activate
           If cell = RemovePartialQtr Then
             a = ActiveCell.Row
             Rows(a & ":" & a).Delete
    
           End If
    Next

    The code works fine in principle.  However, what I'm finding when two consecutive rows meet the criteria for the IF statement, the first one gets deleted, the subsequent rows shift up, and the next row doesn't get analyzed.

    So, if rows 2, 3 and 4 each meet the criteria, then 2 would get deleted, rows 3 and 4 become rows 2 and 3 respectively.  The next cycle of the FOR statement analyzes row 3 (which had been row 4), and leaves row 2 (which had been row 3) not being analyzed.

    How can I step backwards after the delete statement in my code?

    Any help is appreciated!

    Thanks!

    Casey


    Wednesday, January 6, 2016 5:56 PM

Answers

  • If you delete an item from a collection, that collection has been changed and you can't continue to enumerate through it.

    Instead, use a For loop, such as:

    For i = rng.Rows.Count To 1 Step -1
    
            'If cell i in the range contains an "x", delete the entire row.
            If rng.Cells(i).Value = "x" Then rng.Cells(i).EntireRow.Delete
        Next


    Ashish Pandey

    • Marked as answer by Casey_M Wednesday, January 6, 2016 6:21 PM
    Wednesday, January 6, 2016 6:06 PM

All replies

  • If you delete an item from a collection, that collection has been changed and you can't continue to enumerate through it.

    Instead, use a For loop, such as:

    For i = rng.Rows.Count To 1 Step -1
    
            'If cell i in the range contains an "x", delete the entire row.
            If rng.Cells(i).Value = "x" Then rng.Cells(i).EntireRow.Delete
        Next


    Ashish Pandey

    • Marked as answer by Casey_M Wednesday, January 6, 2016 6:21 PM
    Wednesday, January 6, 2016 6:06 PM
  • That worked perfectly, Ashish.

    Thank you!

    Wednesday, January 6, 2016 6:21 PM