=now() -30 days

[Guest]

I would like to set up my worksheet so that if the date in column C is more
than 30 day ago, that entire row will be deleted. Can anyone help?

Thanks,
Steve

 

[Bob Phillips]

How strange!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value < Date - 30 Then
Target.EntireRow.Delete
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[Guest]

From what I can tell this is set up for when I actually type a date in the
cell. i think I was unclear. Everyday I enter information in this worksheet
that must be saved for 30 days. What I would like it to do is be able to run
a macro that auto deletes all entries older than 30 days. For example, today
is June 1, I would like to auto delete all entries on or before May 2. The
column with the dates is column C.

Thanks again,
Steve

 

[Tom Ogilvy]

Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
set lastrow = cells(rows.count,3).End(xlup)
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value > 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub

 

[Guest]

I am getting an error at:
set lastrow = cells(rows.count,3).End(xlup)

it is a compile error: Object required

 

[Bob Phillips]

Try

Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
lastrow = cells(rows.count,3).End(xlup).Row
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value > 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub

 

[Tom Ogilvy]

Thanks for the fix Bob. I seem to make that exact typo too often.

 

[Bob Phillips]

My pleasure Tom.

Regards

Bob

Thanks for the fix Bob. I seem to make that exact typo too often.

--
Regards,
Tom Ogilvy

date May