VBA filter table for max date value in column

[JackDanIce]

Hi,

I have the following code:

Sub Latest_Monday(ByRef tbl As ListObject)
    tbl.Range.AutoFilter field:=3, Operator:=xlFilterValues, Criteria2:=Array(1, CDate(Application.Max(Range("Tbl_Data[Monday]"))))
End Sub

Which is receives an Excel table and is meant to filter column 3 for the max date in column 3; formatted as date, UK settings.

Unfortunately it only filters for every value with month January, the column itself is a formula-column to return the preceding Monday vs a given date with formula:

=WEEKDAY([@[Open Date],2)+1

Can anyone suggest a correction to the code please?

Thank you in advance,
Jack

 

[jasonb75]

Weekday doesn't return a date, just day of week so that formula will never return anything greater than 7, which is the date serial for Jan 7th 1900.

If you want to filter on actual date then you will need to use a column with full dates, not just weekdays.

Also, it might not be filters, but there was one thread I answered not long back where dates in vba needed to be wrapped with CLng in order to return the date serial number, not CDate to return a date.

 

[JackDanIce]

Hi jasonb

My bad, missed the first part of the formula, it should be:

=[@Open Date]-WEEKDAY([@[Open Date],2)+1

Which does return a date, i.e. for this week it's showing 1 March and if I switch formatting to General, it shows it as 44256

Without CDate here, it errors, unable to find at all.
With CDate is generating the behaviour where the filter shows only those dates in January 2021 (I have data from Oct 2020-now)

CDate(Application.Max(Range("Tbl_Data[Monday]")))

 

[jasonb75]

Did you try CLng instead of CDate?

Which error are you seeing?

edit:- brainfart, filters require text strings, I think it has to be in US format as well.

Possibly Format(CDate(Application.Max(Range("Tbl_Data[Monday]")),"mm/dd/yyyy")

 

[JackDanIce]

Without CDate: "1004, AutoFilter method of Range class failed"

Same error if I replace CDate with CLng

Sheet *is* unfiltered prior to this line of code

 

[jasonb75]

Check previous post, I was editing while you replied.

another edit:-

Quick search on google suggests

"=" & CLng(Application.Max(Range("Tbl_Data[Monday]")))

 

[JackDanIce]

Thank you, this now works with code changed to:
tbl.Range.AutoFilter field:=3, Operator:=xlFilterValues, Criteria1:="=" & CDate(Application.Max(Range("Trade_Data[Monday]")))
The use of Array(1, max_date) was placing the date value in the second field of the custom date filter thingy when you do the same through a normal filter on the sheet.
CLng gave same error as before so tried CDate and above works.

Thanks for all your help @jasonb75

 

[steve the fish]

Hi Jack. I didnt quite understand the first sub as its only going to work on the named table in the criteria and column 3. So i thought something like this where you could use it on different tables (so long as they have a heading 'Monday')

Private Sub Latest_Monday(ByRef tbl As ListObject)

tblcol = Application.Match("Monday", tbl.HeaderRowRange, 0)
tblcolmax = Application.Max(Range(tbl.Name & "[Monday]"))
tbl.Range.AutoFilter Field:=tblcol, Operator:=xlFilterValues, Criteria2:=Array(0, CDate(tblcolmax))

End Sub

Sub AutoFilterMonday()

Call Latest_Monday(Sheets("Sheet4").ListObjects("Table1"))

End Sub

Works for me.

 

[JackDanIce]

Hi @steve the fish

Thank you and that would be great, but it's just for a single file (for me!) and have one sheet with a table of data, starting in A1 so was just wanting to automatically on worksheet activate filter to show current week's data! I did edit my code before posting to remove anything unecessary (IMO lol)

But I like the suggestion and going to adapt it into my code