Delete rows in Excel if condition is met.

  oo7juk 11:37 09 Nov 07
Locked

Hi,

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:

=J2<TODAY()

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

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…