how to filter column by last working day in excel

#1 04-08-2016, 05:43 PM

trying to filter column by last working day

that being said if I set a code in macro to do that and I ran macro on Tuesday I will get data filtered for Monday (which would be easy just filter by yesterday but I would like to use same code to be able to ran macro on Monday and get data from Friday before

BobBridges · #2 04-09-2016, 01:26 PM

It seems to me there's a function built into VBA that knows about working days. Let's see, here.... Yeah, here it is: WEEKDAY. You can get WEEKDAY to tell you what day of the week is the date you feed it; the return value is a number 1 through 7 where, by default, 1 is Sunday and 7 is Saturday.

On Sunday you want to you program to subtract 2 days from today's date, so that it can look at Friday; on Mon, subtract 3; on Tue through Sat, just 1. So here's one way to do it:

Code:

vd = Int(Now) 'today's date
vw = Weekday(vd) 'day of the week (1 through 7)
If vw > 2 then vw = 0
TargetDate = vd - vw - 2

I haven't tested this code, but if you work through it for Saturday (7), Sunday (1), Monday (2) and Tuesday (3) I think you'll see it works. There are other ways to do it, of course; this may be one of the simplest.

If your application needs to handle odd work days, you'd have to do it differently.

#3 04-09-2016, 02:49 PM

Thanks
will have to try on Monday
I work from Monday-Friday and I got it when I ran macro Tuesday -Friday (when I can filter by yesterday ) but Monday is the problem as my macro will look for yesterday but that is Sundays data not Friday

macropod · #4 04-09-2016, 08:34 PM

Cross-posted at: http://www.mrexcel.com/forum/excel-q...day-excel.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

#5 04-10-2016, 02:28 PM

sorry about that
I am new to this

#6 02-18-2017, 04:46 PM

i have not figure this one out yet
can anybody help me please
all i am looking is a code in vba to filter column by date in the last used cell (column contains just dates) or filter by older date in that column

BobBridges · #7 02-18-2017, 09:07 PM

So tell me what you tried regarding what I told you back in April, and what happened when you tried it.

#8 02-19-2017, 10:44 AM

no matter what i do with your code i don't get right date in filter
i work from Monday -Friday or sometimes Monday-Saturday
i would like to get code that I CAN RUN on Monday that would filter by Fridays date if Friday is the last working date for past week or Saturday if Saturday is the last working date for past week
but if i ran code on TUASDAY TO FRIDAY ,code would filter by one day before
in other words always filter by older date in that column (column A in my case)

Thanks for your help

BobBridges · #9 02-19-2017, 03:26 PM

Well, send me a copy of the program as you're running it now—you can just attach the .xlsm to this thread—and tell me what happens. I don't mean telling me "it doesn't work", I mean "the program stops at the 23rd line and displays the message '...' " or "when I start the program it refuses to run, highlighting the statement '...' " or something specific.

#10 02-19-2017, 05:24 PM

there is 2 macros in the ATTACHED FILE
macro 1 and macro 3

Thank you so much for your help

NoSparks · #11 02-20-2017, 10:34 AM

This doesn't use Filter, but, I think it might accomplish what you're after.

Code:

Sub Show_Last_Day_Only()
    Dim lr As Long
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Rows("3:" & lr - 1).Hidden = True
End With
End Sub

and to bring everything back

Code:

Sub Show_All_Days()
    Dim lr As Long
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Rows("3:" & lr - 1).Hidden = False
End With
End Sub

#12 02-20-2017, 11:06 AM

thank you so much for your help
this is great but I forgot to say that there is more then one row with same date
or it could be like this bellow in the same row-I will need all from same date to show
02/15/17
02/15/17
02/16/17
02/16/17
02/16/17
02/16/17
02/17/17
02/17/17
02/17/17
02/17/17
02/17/17
02/17/17
02/17/17

NoSparks · #13 02-20-2017, 11:50 AM

Oh... now I understand why filter, try this

Code:

Sub Filter_For_Last_Date()
    Dim lr As Long
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("A2:A" & lr).AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:="=" & .Cells(lr, 1).Value
End With
End Sub

#14 02-20-2017, 02:38 PM

perfect
I had to change format on my cells for date but it looks like you got it on my test sheet
now when i try to do on my work sheet is not working since formatting is like this 1/17/2017 20:45:49 PM (and i cant change that)
do you know what needs to be changed (like I said i cant change formatting of the cells on the original sheet)
Thank you so so much

NoSparks · #15 02-20-2017, 04:14 PM

The best way to get a solution for what you're actually working with is to post a sample sheet with what you are actually working with.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Report chart filter not working	Asita	Project	5	07-07-2015 01:08 PM
Contact view filter not working	gebobs	Outlook	2	03-10-2014 06:28 AM
Range Name in Adv Filter -> NOt working	56_kruiser	Excel	2	11-20-2012 09:58 AM
drop down menu that control filter on other column	massi	Excel	0	08-24-2010 01:13 PM
junk email filter not working	michmurp	Outlook	0	09-15-2008 10:57 AM