How do I ignore Saturdays and Sundays with Autofilter?

[bspires]

Hi Guys,

I hope someone can help me??

In a workbook I have a function that autofilters by column D if it is less than todays date or is blank. This is great except on a Monday because I need it to ignore weekends.

E.g. If today is Monday, autofilter to show anything with last fridays date or is blank, otherwise show anything with yesterdays date or is blank.

The date in Column D is formatted dd/mm/yyyy.

I'm sure this should be relatively simple but I can't get my head round it. I've been playing with it all day at work and I'm now sat at home and still thinking about it!!!

I hope I've explained that OK??

If anyone can help I'd be very grateful.

Many Thanks
Ben

P.S. If I can enter in a predetermined set of public holiday dates to ignore too that would be even better!

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim mpLastRow As Long
Dim rng As Range
With ActiveSheet

mpLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Columns(5).Insert
.Range("E1").Value = "temp"
Set rng = .Range("E2").Resize(mpLastRow - 1)
rng.Formula = "=OR(D2="""",D2=WORKDAY(TODAY(),-1))"
.Columns(5).AutoFilter field:=1, Criteria1:="=TRUE"
End With

End Sub