Delete row after 120 days

AC

Board Regular
Joined
Mar 21, 2002
Messages
153
I have a date in column C, I want to delete the whole row when this date is 120 days old based on the current day,=Now(). Can this be done? Thanks I am using Excel '97
This message was edited by ac on 2002-03-22 12:19
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Apply an Advanced AutoFilter with a Criteria range containing the formula, =TODAY()-'Date'>=120. Delete the displayed records. For more on the specification of criteria see the Excel Help topic for "Examples of advanced filter criteria".
This message was edited by Mark W. on 2002-03-22 12:36
 
Upvote 0
Right-click on your sheet tab, select view code and paste in the code below. This assumes your date is in cell C1 and will be run whenever you change something on the sheet (although it can be modified to run when the workbook is opened, the sheet is activated etc etc.): -

Private Sub Worksheet_Change(ByVal Target As Range)

If DateDiff("d", [c1].Value, Now) >= 120 And [c1].Value <> "" _
Then [c1].EntireRow.Delete shift:=xlUp

End Sub
 
Upvote 0
MudFace, I like your approach it works for C1, I want to delete the rows for all the dates in column C from C3:C4000, Can this be done?
 
Upvote 0
The following should work OK, though I'm sure there's a better way: -

In the VBA editor, insert a module and then copy and paste the following code: -

Public Sub DeleteRows()

Dim rw As Integer
Dim x As Integer

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual

rw = Range("C65536").End(xlUp).Row

For x = rw To 3 Step -1
If DateDiff("d", Range("c" & x).Value, Now) >= 120 And Range("c" & x).Value <> "" _
Then Rows(x).EntireRow.Delete shift:=xlUp
Next x
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub
 
Upvote 0
Mudface
Will probably give a type-mismatch error if an invalid date is entered?
 
Upvote 0
Sorry, Tom, that won't work properly, it will only delete every other row (try filling in 4000 rows from 1/1/2000 onwards to see). You need to go backwards with this sort of thing. Also, your message will pop up every time a row is deleted, unless you remove it from inside the loop.

Ooops, right about the type-mismatch error, try this instead: -

Public Sub DeleteRows()

Dim rw As Integer
Dim x As Integer

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual

rw = Range("C65536").End(xlUp).Row

For x = rw To 3 Step -1
If IsDate(Range("c" & x).Value) Then
If DateDiff("d", Range("c" & x).Value, Now) >= 120 And Range("c" & x).Value<> "" _
Then Rows(x).EntireRow.Delete shift:=xlUp
End If
Next x
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub
This message was edited by Mudface on 2002-03-22 13:41
 
Upvote 0
Here's more efficient code (avoids using a loop) :-

Dim rng As Range
Set rng = Range([C1], [C65536].End(xlUp))
Application.ScreenUpdating = False
rng.EntireColumn.Insert
With rng.Offset(0, -1)
.FormulaR1C1 = "=IF(RC[1]+120<=NOW(),1,"""")"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
On Error GoTo 0
.EntireColumn.Delete
End With
This message was edited by C. O. Jones on 2002-03-22 18:07
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top