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


For i = lastrow To 2 Step -1

1 Answer
1

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.

Union()

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.