Delete rows in excel based on day and date

[julesl]

Hi

I have a spreadsheet which contains 13 columns, one of which is a start date (column D). The spreadsheet will contain rows with varying start dates and I need to remove all dates that are before yesterday's date except on Monday's when data for the previous Friday, Saturday and Sunday should remain in the spreadsheet.

I've tried adapting the code below for non-monday's, which I've been using to remove other data, but haven't managed to get anywhere (as my VB knowledge is almost non-existant) :-

Sub DeleteRowsBasedOnstartDate()
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=3, Criteria1:="Today-1"
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

Any help would be gratefully appreciated

Thanks

Jules

 

[SkipVought]

Jules,

What does this mean?

...except on Monday's when data for the previous Friday, Saturday and Sunday should remain in the spreadsheet.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[julesl]

Hi

I have an extract which is run each week day evening and provided to me the following morning.

On Tuesday to Friday I need to extract all the policies that changed to a status of inforce on the day the extract was run e.g on Tuesday morning I need to extract all the policies that went to a status of inforce on Monday.

On Sunday night the extract will be run ready for Monday morning and I need to extract all the policies that changed to a status of inforce on the Friday, Saturday & Sunday (as the extract will not be run on these days).

Hope this is a better explanation

Thanks

Jules