CODEWITHSUNDEEP
vbaexcel
Ibrahim
Ibrahim

Reputation: 79

Deleting rows based on a column value

I want to check if a cell ("C") does not contain a date or is empty to delete the specific row. My code does not delete all the specifics rows and seems too slow for the number of rows (138).

lastrow = pertes.Cells(pertes.Rows.count, "A").End(xlUp).Row
For i = 2 To lastrow
    If Not IsDate(pertes.Cells(i, 3).Value) Or IsEmpty(pertes.Cells(i, 3).Value) Then
        pertes.Rows(i).Delete
    End If
Next i

Upvotes: 1

Views: 78

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

If you delete or add rows one-by-one your loop must be backwards: 

For i = lastrow To 2 Step -1

otherwise deleting changes row count and therefore the loop counts wrong.

To make it faster you can collect all rows that have to be deleted in a range using Union(), and then delete them all at once in the end. This is faster because every delete action takes its time, and it reduces the amount of actions to 1.

Option Explicit

Public Sub DeleteRows()
    Dim LastRow As Long
    LastRow = pertes.Cells(pertes.Rows.Count, "A").End(xlUp).Row

    Dim RowsToDelete As Range '… to collect all rows

    Dim i As Long
    For i = 2 To LastRow 'forward or backward loop possible
        If Not IsDate(pertes.Cells(i, 3).Value) Or IsEmpty(pertes.Cells(i, 3).Value) Then
            If RowsToDelete Is Nothing Then 'collect first row
                Set RowsToDelete = pertes.Rows(i)
            Else 'add more rows to that range
                Set RowsToDelete = Union(RowsToDelete, pertes.Rows(i))
            End If
        End If
    Next i

    RowsToDelete.Delete 'delete all rows at once
End Sub

Note that here we don't need to loop backwards because we delete all rows at once after the loop.

Upvotes: 2

Related Questions