Delete rows in Excel if condition is met.

  oo7juk 11:37 09 Nov 2007


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 2007

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.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?