Excel VBA deleting rows in a for loop misses rows

Question

I have a subroutine that deletes rows in a range containing around 1000 rows. Rows are deleted on a critera. The code below works.

However, when I run the macro I usually have to run it 4 times before all rows containing the removal criteria are removed.

I guess this is because the for loop misses its index when a row suddenly dissapears when deleting a row.

My first code looks like this.

Set StatusRange = Range("B2", Range("B2").End(xlDown))

For Each StatusCell In StatusRange
    If StatusCell = "FG" Then
        StatusCell.EntireRow.Delete
    ElseIf StatusCell = "QC" Then
        StatusCell.EntireRow.Delete
    ElseIf StatusCell = "CS" Then
        StatusCell.EntireRow.Delete
    Else
    End If
Next StatusCell

When I try to update the range each loop, it still doesn't work.

Set StatusRange = Range("B2", Range("B2").End(xlDown))

For Each StatusCell In StatusRange
    If StatusCell = "FG" Then
        StatusCell.EntireRow.Delete
    ElseIf StatusCell = "QC" Then
        StatusCell.EntireRow.Delete
    ElseIf StatusCell = "CS" Then
        StatusCell.EntireRow.Delete
    Else
    End If
                
    Set StatusRange = Range("B2", Range("B2").End(xlDown))
Next StatusCell

Is there anyone who know a sloution to this?

Work from teh bottom up. If you delete a row, everything moves up and yuou skip that row on teh next iteration. — user4039065
– user4039065, Commented Apr 17, 2017 at 15:11
This is almost certainly a duplicate question and I'll mark it as such when I find the suitable duplicate. Whenever you delete items from a collection, you must delete from Rows.Count to 1 Step -1 otherwise you will skip rows. — David Zemens
– David Zemens, Commented Apr 17, 2017 at 15:12
Go ahead and dupe this @DavidZemens - I've added some value by showing the Case statement for multiple comparisons but this is still a dupe. — user4039065
– user4039065, Commented Apr 17, 2017 at 15:16
Here's a good explanation: stackoverflow.com/questions/23689196/… — David Zemens
– David Zemens, Commented Apr 17, 2017 at 15:17
And another with an accepted answer which actually appears to be VB.NET code but the principle is the same: stackoverflow.com/questions/10315120/… — David Zemens
– David Zemens, Commented Apr 17, 2017 at 15:17

score 16 · Accepted Answer · 2017-04-17 15:24:34Z

16

Work from the bottom up. If you delete a row, everything moves up and you skip that row on the next iteration.

Here is the 'guts' of the code to work up from the bottom.

With Worksheets("Sheet1")
    For rw = .Cells(.Rows.Count, "B").End(xlUp).Row To 2 Step -1
        Select Case UCase(.Cells(rw, "B").Value2)
            Case "FG", "QC", "CS"
                .Rows(rw).EntireRow.Delete
        End Select
    Next rw
End With

edited Apr 17, 2017 at 15:24

answered Apr 17, 2017 at 15:16

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

David Zemens Over a year ago

I was writing literal same answer (complete with Case statement) haha :D

Shai Rado Over a year ago

Damn, I was just finished typing and posting almost the exact same code ;)

Shai Rado · Accepted Answer · 2017-04-17 15:20:04Z

4

Since there's no Reverse loop for For Each you need to use a slightly different approach.

Also, your code with multiple Ifs and OR is "screaming for the use of Select Case.

Dim StatusRange As Range
Dim i As Long

Set StatusRange = Range("B2", Range("B2").End(xlDown))

' loop backward when deleting Ranges, Rows, Cells
For i = StatusRange.Rows.Count To 1 Step -1
    Select Case StatusRange(i, 1).Value
        Case "FG", "QC", "CS"
            StatusRange(i, 1).EntireRow.Delete
        Case Else ' for the future if you need it

    End Select
Next i

answered Apr 17, 2017 at 15:20

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Comments

Victor Porée · Accepted Answer · 2020-02-11 15:37:28Z

4

Wait til the For Each loops ends before deleting the rows:

Option Explicit

Sub mySub()

    Dim myRange As Range
    Set myRange = ActiveSheet.UsedRange

    Dim myCell As Range
    Dim myTrash As Range

    For Each myCell In myRange
        Select Case myCell.Value
            Case "my value"
                If myTrash Is Nothing _
                    Then
                        Set myTrash = myCell.EntireRow
                    Else
                        Set myTrash = Union(myTrash, myCell.EntireRow)
                End If
        End Select
    Next

    If Not myTrash Is Nothing Then myTrash.Delete

End Sub

edited Feb 11, 2020 at 15:37

answered Jan 16, 2020 at 3:42

Victor Porée

513 bronze badges

1 Comment

snibbo Over a year ago

For me this was actually the best solution. I just added If rngToBeDeleted Is Nothing Then Set rngToBeDeleted = xlRow.EntireRow before the Union() to avoid an error when the range is not set initially.

Machiel · Accepted Answer · 2021-10-30 11:01:36Z

-1

start_over: s = 0  
    for each cell  
        if cell = "FG" then   
           s = 1            'Marked to start over if ifstmt was true
           [other code]
        end if  
    next cell  
   If s = 1 Then GoTo start_over

'your code:

Set StatusRange = Range("B2", Range("B2").End(xlDown))  
    start_over: s = 0  
    For Each StatusCell In StatusRange  
        If StatusCell = "FG" or StatusCell = "QC" or StatusCell = "CS" Then StatusCell.EntireRow.Delete: s=1  
    Next StatusCell  
   If s = 1 Then GoTo start_over

edited Oct 30, 2021 at 11:01

answered Oct 30, 2021 at 10:39

Machiel

11 bronze badge

Collectives™ on Stack Overflow

Excel VBA deleting rows in a for loop misses rows

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related