VBA filter table for max date value in column

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have the following code:

VBA 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:
Excel Formula:
=WEEKDAY([@[Open Date],2)+1

Can anyone suggest a correction to the code please?

Thank you in advance,
Jack
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Hi jasonb

My bad, missed the first part of the formula, it should be:
Excel Formula:
=[@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)
VBA Code:
CDate(Application.Max(Range("Tbl_Data[Monday]")))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
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")
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
Check previous post, I was editing while you replied.

another edit:-

Quick search on google suggests
VBA Code:
"=" & CLng(Application.Max(Range("Tbl_Data[Monday]")))
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 :)
 
Solution

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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')

VBA Code:
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,493
Messages
5,636,639
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top