AutoFilter multiple criteria if date = Friday 2

[ryplew]

PrevWorkDay is a date. If the date is NOT a Friday, I would like to filter to show all dates besides PrevWorkDay (this works correctly in the Else statement). If PrevWorkDay IS a Friday, I would like to filter to show all dates besides PrevWorkDay, as well as that following Saturday, and that following Sunday. I have a start below, but I'm assuming that my issue lies in me not using vbFriday or the associated AutoFilter correctly.

Thanks for your time!

If PrevWorkDay = vbFriday Then
        With rng
            .AutoFilter Field:=1, Criteria1:=Array("<>" & PrevWorkDay, "<>" & PrevWorkDay + 1, "<>" & PrevWorkDay + 2), Operator:=xlAnd
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
Else
        With rng
            .AutoFilter Field:=1, Criteria1:="<>" & PrevWorkDay
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
End If

 

[Andrzejek]

PrevWorkDay is a date", but the VB constant vbFriday is NOT a date, so you compare a date of (for example: today) 9/12/2016 to 6 (a value of vbFriday)

Try:

If [blue]Weekday([/blue]PrevWorkDay[blue])[/blue] = vbFriday Then

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[ryplew]

Indeed! Thank you, Andy. I just passed right over that!

As for the filter...now it is filtering to only show Sunday's date. Is my operator incorrect, perhaps? I've tried xlAnd and xlOr, both resulting in only showing Sunday's date. To reiterate, I need to show all dates besides Friday, Saturday, and Sunday.

Thanks!

 

[SkipVought]

'
     Weekday(PrevWorkDay) = vbFriday Then
        With rng
            .AutoFilter  Field:=1, Criteria1:= _
        "<" & Format([PrevWorkDay], "m/d/yyyy"), Operator:=xlOr, Criteria2:=">" & Format([PrevWorkDay] + 2, "m/d/yyyy")
        End With
'......

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Also, you have [tt].EntireRow.Delete[/tt] in the If and Else part, I would put it outside of the If statement:

With rng
    If Weekday(PrevWorkDay) = vbFriday Then 
        .AutoFilter  Field:=1, Criteria1:= _
        "<" & Format([PrevWorkDay], "m/d/yyyy"), Operator:=xlOr, _
        Criteria2:=">" & Format([PrevWorkDay] + 2, "m/d/yyyy")
    Else
        .AutoFilter Field:=1, Criteria1:="<>" & PrevWorkDay
    End If 
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

When I see the code that looks the same, I try to find the differences. If I don't find any differences, I ask: why is it in 2 (or more) places?

Or is it just me....?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[ryplew]

Thank you both!

 

[SkipVought]

Glad to help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]