Delete rows in Excel if condition is met.

  oo7juk 11:37 09 Nov 07


I have a workbook that contains rows of data. The last cell/column in the row is 'J1' and so on.

When J1 contains an expired date I would like the row to automatically be moved to another sheet or just deleted.

The formula used in column J is =DATE(YEAR(F2),MONTH(F2)+LEFT(I2,FIND(" ",I2,1)),DAY(F2))

Many thanks,

  VoG II 11:46 09 Nov 07

I don't see why this won't work provided that the values in column J are actually dates.

Sub DLExpired()
Dim lastrow As Long, i As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
lastrow = Cells(Rows.Count, 10).End(xlUp).Row
For i = lastrow To 1 Step -1
If Range("J" & i).Value < Date Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

You could use a formula to check which rows should be deleted:


This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5