delete rows containing old dates/times

**mattmac** · 04-01-2009, 07:17 AM

Hi guys,
I need help deleting rows which contain a cell with date / time in the format dd/mm/yyyy hh:mm if the value is older than the current date / time

any ideas?

thanks

**DonkeyOte** · 04-01-2009, 09:13 AM

More info please...

Where are the dates located (range-wise) ?

Are the date time values listed in any sort of order ?

Is the Current Date always listed ?

**mattmac** · 04-01-2009, 09:35 AM

Sorry,
dates are in column S and there could be any number of rows.

dates are in format dd/mm/yyyy hh:mm (all in one column) and they have not been soretd.

The current date / time isn't listed, I just want to pull that from the system clock.

Thanks

**DonkeyOte** · 04-01-2009, 10:03 AM

One way.. possibly...

Code:

Sub PurgeOld()
Dim rngData As Range
Set rngData = Range(Cells(1, "S"), Cells(Rows.Count, "S").End(xlUp))
With rngData
    .AutoFilter Field:=1, Criteria1:="<" & CDbl(Now)
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With
Set rngData = Nothing
End Sub

Assumes S1 is a header value and first datetime value appears in S2

**mattmac** · 04-01-2009, 11:17 AM

Acutally i'm getting an error:

runtime error 1004

autofilter method of range class failed

at this bit:
.AutoFilter Field:=1, Criteria1:="<" & CDbl(Now)

**shekar goud** · 04-01-2009, 11:57 AM

Hi,

Try this :

Code:

Sub checkfordate()
Dim counter As Long
Dim i As Long
Dim rownum As Long

counter = 0
rownum = ActiveSheet.UsedRange.Rows.count - 1 + ActiveSheet.UsedRange.Rows(1).Row
For i = rownum To 1 Step -1

If Cells(i, 19) < Now Then
Rows(i).Delete
End If


counter = counter + 1
Next i
End Sub

Regards,
Shekar.