CODEWITHSUNDEEP
vbaexcelfor-loopdelete-rowstrikethrough
Da Spotz
Da Spotz

Reputation: 200

"entirerow.delete" skips entries in For loop

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.

Upvotes: 1

Views: 1888

Answers (2)

Da Spotz
Da Spotz

Reputation: 200

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

Upvotes: 2

Dave
Dave

Reputation: 4356

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

Upvotes: 0

Related Questions