I've got a database sheet of about 3000 rows with a date column (values from
2004 up to yesterday).
When I run this:
If cell.Value < (CDate(Now) - 2) Then cell.Interior.ColorIndex = 6
all the cells up to three days ago turn yellow, as they should.

But when i run this:
If cell.Value < (CDate(Now) - 2) Then EntireRow.Delete
about half the rows are deleted but I still have hundreds of dates from
years ago.
I've tried splitting the "If" into:
If cell.Value < (CDate(Now) - 2) Then
EntireRow.Select
Selection.Delete
End if
but the result is the same.

Can anyone explain why I'm getting different results and how this apparently
random selection is taking place?

--
WinXP - Office2003 (Italian)

It will be something to do with your loop around the rows, because when you
do an EntireRow.Delete then rows below will move up. Are you remaining on the
current row after a delete ? or moving 1 row down which would miss the row
below that has just moved up ?

"David Macdonald" wrote:

I've got a database sheet of about 3000 rows with a date column (values from
2004 up to yesterday).
When I run this:
If cell.Value < (CDate(Now) - 2) Then cell.Interior.ColorIndex = 6
all the cells up to three days ago turn yellow, as they should.

But when i run this:
If cell.Value < (CDate(Now) - 2) Then EntireRow.Delete
about half the rows are deleted but I still have hundreds of dates from
years ago.
I've tried splitting the "If" into:
If cell.Value < (CDate(Now) - 2) Then
EntireRow.Select
Selection.Delete
End if
but the result is the same.

Can anyone explain why I'm getting different results and how this apparently
random selection is taking place?

--
WinXP - Office2003 (Italian)

It sounds like you need to reverse your loop. Plus it looks like you are
using a For Each...Next Loop, right? You can only reverse a loop with the
For...Next Loop. I wrote some code that should help. I assumed your dates
you want to scan are in Col.A. This code will find the last cell with data
in it in Col A, scan all cells up to the header row (row 1) and delete the
entire row if the If...Then statement is True. Hope this helps! If so, let
me know, click "YES" below.

Sub DeleteRows()

Dim i As Long
Dim LastRow As Long

' finds last row in column A
LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

For i = LastRow To 2 Step -1
If Cells(i, "A").Value < (CDate(Now) - 2) Then Rows(i).Delete
Next i

End Sub

--
Cheers,
Ryan


"David Macdonald" wrote:

I've got a database sheet of about 3000 rows with a date column (values from
2004 up to yesterday).
When I run this:
If cell.Value < (CDate(Now) - 2) Then cell.Interior.ColorIndex = 6
all the cells up to three days ago turn yellow, as they should.

But when i run this:
If cell.Value < (CDate(Now) - 2) Then EntireRow.Delete
about half the rows are deleted but I still have hundreds of dates from
years ago.
I've tried splitting the "If" into:
If cell.Value < (CDate(Now) - 2) Then
EntireRow.Select
Selection.Delete
End if
but the result is the same.

Can anyone explain why I'm getting different results and how this apparently
random selection is taking place?

--
WinXP - Office2003 (Italian)

Divide the prize money equally between the three of you!
--
WinXP - Office2003 (Italian)


"David Macdonald" wrote:

I've got a database sheet of about 3000 rows with a date column (values from
2004 up to yesterday).
When I run this:
If cell.Value < (CDate(Now) - 2) Then cell.Interior.ColorIndex = 6
all the cells up to three days ago turn yellow, as they should.

But when i run this:
If cell.Value < (CDate(Now) - 2) Then EntireRow.Delete
about half the rows are deleted but I still have hundreds of dates from
years ago.
I've tried splitting the "If" into:
If cell.Value < (CDate(Now) - 2) Then
EntireRow.Select
Selection.Delete
End if
but the result is the same.

Can anyone explain why I'm getting different results and how this apparently
random selection is taking place?

--
WinXP - Office2003 (Italian)