Filter for Last Weekday

**dcoley** · 12-26-2013, 12:56 PM

Hey, guys. I'm new here and to VBA so go easy on me

I searched the forums but couldn't find exactly what I'm looking for so I thought I'd ask for help.

I've set up a macro and need it to filter a column for the last weekday. I could have used yesterday except that it would do me no good on Mondays when yesterday would have been a Sunday. So, I need it to filter for the last weekday only. That code that I used is pasted below but continues to get an error message. Any help is very much appreciated.

ActiveSheet.Range("$A:$AF").AutoFilter Field:=17, Criteria1:=Weekday(Now(), -1)

Dennis

**PCI** · 12-26-2013, 01:27 PM

A suggestion that you certainly thought about:
Add an extra column with the weekday and filter on this extra column.

**jaslake** · 12-26-2013, 01:29 PM

Hi dcoley

Welcome to the Forum!!!

I'll go easy on you and remind you to ALWAYS wrap any Code you post in Code Tags

This Code returns 4 in the immediate window

Please Login or Register  to view this content.

This Code returns Wednesday in the immediate window

Please Login or Register  to view this content.

**dcoley** · 12-26-2013, 03:04 PM

John, thank you for that reply (and for going easy on me). So, I thought that had done the trick, John, but I'm getting no results returned to me, but when I filter it myself I get some returns. What should happen is that on say December 26, 2013 when I run the macro, I want to get the results from only December 25th, 2013. If it happens to fall on a Monday when I run the macro, I want it to pull the results for the previous Friday. Does that make sense?

For some reason, when I run the macro with the code as you listed above, I get no results at all.

Because this is a shared spreadsheet and the information is fed into it, I am not at liberty to add another column as was suggested earlier.

**jaslake** · 12-26-2013, 06:03 PM

Hi Dennis

Since I'm working out of context I'm not able to debug the Code. I'll be happy to do so but you'll need to post a sanitized version of your File. To do so...

[Canned Reply]
Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet (if appropriate) that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.