Filter by Date

[colbecd]

Hi

I have a formula in VBA that will filter by date + 1 or date + 2 depending on the situation.

The problem i have is that on a Friday i want date + 1 skip the weekend and filter for the Monday and for date + 2 to filter for the Tuesday.

Is any one able to help? Would be greatly appreciated!

 

[AlphaFrog]

Something like this will generate the next two weekdays (M-F).

    d = [COLOR=darkblue]Date[/COLOR] [COLOR=green]'Startdate[/COLOR]
    
    d1 = Evaluate("Workday(" & [COLOR=darkblue]CLng[/COLOR](d) & ", 1)")    [COLOR=green]' Next weekday[/COLOR]
    d2 = Evaluate("Workday(" & [COLOR=darkblue]CLng[/COLOR](d) & ", 2)")    [COLOR=green]' 2nd weekday[/COLOR]

The Workday function has an optional Holiday list argument as well.

 

[colbecd]

Thanks for coming back to me do you know how i would use the above in the below code?

Sub Filterbydate()
Sheets("Report").Select
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$1775").AutoFilter Field:=6, Operator:= _
xlFilterValues, Criteria2:=Array(2, Date + 2)
Rows("2:10000").Select
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
Range("A1").Select
End Sub

Thank you!

 

[AlphaFrog]

It's not obvious what you want to do. Below a guess. It filters dates in column F between the 1st and 2nd weekday after the start date.

[COLOR=darkblue]Sub[/COLOR] Filterbydate()
    
    [COLOR=darkblue]Dim[/COLOR] lStartDate [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], lWeekDay1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], lWeekDay2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    lStartDate = Date   [COLOR=green]'Start date[/COLOR]
    lWeekDay1 = Evaluate("Workday(" & lStartDate & ", 1)")    [COLOR=green]' Next weekday[/COLOR]
    lWeekDay2 = Evaluate("Workday(" & lStartDate & ", 2)")    [COLOR=green]' 2nd weekday[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Sheets("Report")
        .AutoFilterMode = [COLOR=darkblue]False[/COLOR] [COLOR=green]'Clear all autofilters[/COLOR]
        [COLOR=green]' Filter on dates in column F[/COLOR]
        .Range("A1:I1775").AutoFilter _
            Field:=6, _
            Criteria1:=">=" & lWeekDay1, _
            Operator:=xlAnd, _
            Criteria2:="<=" & lWeekDay2
                
     [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
     
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]