Delete Rows Based on Current Date

[oliverralph7]

I am trying to write some code and I am lost.
Column Z-has a Date entered

I need the macro to check If Column Z has a date within the last two days it will delete the row.
The problem that I am having is that I have to count Saturday and Sunday as 1 day.

If Current Date=Monday it will count Saturday and Sunday as Day 1 and then Friday as Day 2
If Current Date=Tuesday it will count Monday as Day 1 and then Saturday and Sunday as Day 2
If Current Date=Wednesday it will count Tuesday as Day 1 and then Monday as Day 2
If Current Date=Thursday it will count Wednesday as Day 1 and then Tuesday as Day 2
If Current Date=Friday it will count Thursday as Day 1 and then Wednesday as Day 2

I am not sure how to get the macro to work when there is a Saturday and Sunday.

There are 3000 rows on the spreadsheet and the macro would need to go check all the rows.

[mdmackillop]

Please post the code you have.

[Bob Phillips]

You want to ignore weekends?



Sub DeleteDates()
Dim LastRow As Long
Dim DatePast As Date
Dim i As Long

With ActiveSheet

Select Case Weekday(Date, vbMonday)

Case Is = 1, 2: DatePast = Date - 4
Case Is = 6: DatePast = Date - 2
Case 7: DatePast = Date - 3
Case Else: DatePast = Date - 2
End Select

LastRow = .Cells(.Rows.Count, "Z").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "Z").Value >= DatePast Then

.Rows(i).Delete
End If
Next i
End With
End Sub

[oliverralph7]

I am looking to count Saturday and Sunday as 1 day instead of two days on Monday and Tuesday.

[Bob Phillips]

So if today is Monday 29th July, what dates should be deleted?

If today is Sun 28th, what dates? Sat 27th? Fri 26th?

[oliverralph7]

If today is Monday 28th of July it would delete Sun 27th, Sat 26th, and Friday 25th.

If today was Tuesday 29th of July it would delete Mon 28th, Sun 27th, and Sat 26th.

If today was Wednesday 30th of July it would delete Tues 29th and Monday 28th.

If today was Thursday 31st of July it would delete Wed. 30th and Tues 29th.

If today was Friday 1st of August it would delete Thursday 31st and Wednesday 30th.

If today was Saturday or Sunday the macro would not be used.

[Oorang]

What about holidays?

[Bob Phillips]

Notwithstanding Aaron's question about holidays



Sub DeleteDates ()
Dim LastRow As Long
Dim DatePast As Date
Dim i As Long

With ActiveSheet

Select Case Weekday(mDate, vbMonday)

Case Is = 1, 2: DatePast = mDate - 3
Case Else: DatePast = mDate - 2
End Select


LastRow = .Cells(.Rows.Count, "Z").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "Z").Value >= DatePast Then

.Rows(i).Delete
End If
Next i
End With
End Sub