CODEWITHSUNDEEP
excelvba
FotoDJ
FotoDJ

Reputation: 351

Filter Data Using AutoFilter Criteria for all records in column from yesterday workday

This is the code filtering column by date, showing only yesterday's records. How can I modify that code to show workdays, so if I run it on Monday it would show Fridays records? Is it possible?

Selection.AutoFilter
    ActiveSheet.Range("$F$1:$F$50").AutoFilter Field:=1, Criteria1:=2, _
        Operator:=11, Criteria2:=0, SubField:=0

Upvotes: 2

Views: 204

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

Based on my Regional settings:

Sub qwerty()
    Dim datestring As String, d As Date, sr As String

    d = Now()
    d = d - 1
    sr = Format(d, "dddd")
    If sr = "Saturday" Then d = d - 1
    If sr = "Sunday" Then d = d - 2
    datestring = "=" & Format(d, "mm/dd/yyyy")

    ActiveSheet.Range("$F$1:$F$50").AutoFilter Field:=1, Criteria1:= _
        datestring, Operator:=xlAnd

End Sub

Before:

enter image description here

After:

enter image description here

Upvotes: 1

Related Questions