"entirerow.delete" skips entries in For loop

Question

I'm trying to clean up a set of data and noticed something strange with vba function entirerow.delete The following code will, as intended, delete the entire row if it is in the format strikethrough, but will skip the rows immediately following it, if they are also in that format. It seems like a it takes a row that is not in the strikethrough format to "reset" the ability to delete more rows. Does anyone know why, or what I could do to debug this?

For Each rng In rng1
'Check each character in the cell
    For i = 1 To Len(rng.Value)
'If any letter is Strikethrough,delete entire column
        If rng.Characters(i, 1).Font.Strikethrough = True Then
            rng.Select    'just serves the purpose of observing which rows are being selected
            rng.EntireRow.Delete
        GoTo NextRng
        End If
    Next i
NextRng:
Next rng

I should say that I have found a workaround using a different approach, but it is very slow:

'Delete cells that have the strikethrough format - works but is super slow!
ws2.Range("B2").Activate
Do Until ActiveCell.Value = ""
    If ActiveCell.Font.Strikethrough = True Then
        ActiveCell.EntireRow.Delete
        Else: ActiveCell.Offset(1, 0).Activate
    End If
Loop

If anyone has an alternative method to solve this issue that is also fast, I'd also be immensely grateful for your input.

You should never delete rows in a loop like that. Use a reverse loop. Search stackoverflow. I have answered a post before — Siddharth Rout, Sep 21 '16 at 15:26
see my [post](http://stackoverflow.com/questions/19241905/vba-conditional-delete-loop-not-working) You can also use a range object to identify the rows you want to delete. It is much faster than deleting rows in a loop — Siddharth Rout, Sep 21 '16 at 15:51
Thanks for the tip about reverse looping, but now that it's working it is also very slow. @Siddharth Rout: I will look into your post now to see if I can make sense of your method — Da Spotz, Sep 21 '16 at 16:24

score 2 · Accepted Answer · edited May 23 '17 at 12:15

Thanks to all of your quick responses I figured it out. Special thanks @Siddarth Rout for nudging me towards a (slightly) quicker method on this thread here: VBa conditional delete loop not working Here's the working code in case anyone is curious:

Dim delRange As Range
Dim ws2 As Worksheet
Dim i As Long

'Find Lastrow in ws2
LastRow2 = ws2.Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
With ws2
    For i = 1 To LastRow2
        If .Cells(i, 2).Font.Strikethrough = True Then
'This if statement adds all the identified rows to the range that will be deleted
            If delRange Is Nothing Then
                Set delRange = .Rows(i)
            Else
                Set delRange = Union(delRange, .Rows(i))
            End If
        End If
    Next i

    If Not delRange Is Nothing Then delRange.Delete
End With

++ Good Job on arriving at a solution :) – Siddharth Rout Sep 21 '16 at 16:48 — Siddharth Rout, Sep 21 '16 at 16:48

Dave · Answer 2 · 2016-09-21T16:45:15.177

Find the end of your range:

Dim wSheet As Worksheet : Set wSheet = ThisWorkbook.Worksheets("Sheetname")
Dim lastRow
' gets the last row in col 1, adjust as required
lastRow = wSheet.Cells(wSheet.Rows.Count, 1).End(xlUp).Row

Now perform a For loop, backwards. The issue you are facing is that when you delete a row, the data moves up (for example: row 56 gets deleted, row 57 becomes 56). The solution is to delete from the bottom up.

For myLoop = lastRow to 2 Step -1 ' or to 1 if you've no header row
    Set myRange  = wSheet.Range("A" & myLoop)
    For mySubLoop = 1 To Len(myRange.Value)
        'If any letter is strikethrough,delete entire row 
        If myRange.Characters(mySubLoop, 1).Font.Strikethrough = True Then
            myRange.EntireRow.Delete
            Exit For ' skip out of this inner loop and move to the next row
        End If
    Next
Next

@ThomasInzina Thanks for the catch, much appreciated! – Dave Sep 21 '16 at 16:45 — Dave, Sep 21 '16 at 16:45

"entirerow.delete" skips entries in For loop

2 Answers2

Linked

Related