INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

AutoFilter multiple criteria if date = Friday

AutoFilter multiple criteria if date = Friday

(OP)
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!

CODE -->

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 

RE: AutoFilter multiple criteria if date = Friday

"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:

CODE

If Weekday(PrevWorkDay) = vbFriday Then 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: AutoFilter multiple criteria if date = Friday

(OP)
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!

RE: AutoFilter multiple criteria if date = Friday

CODE

'
     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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: AutoFilter multiple criteria if date = Friday

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

CODE

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....? smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: AutoFilter multiple criteria if date = Friday

(OP)
Thank you both!

RE: AutoFilter multiple criteria if date = Friday

Glad to help.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close