Excel filter by latest date

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Excel filter by latest date

Post by Lost Paul »

I'm trying to automate a user(?) file so that when a sheet is updated, the results are filtered by the latest date.

There are title & other rows above the data which precludes using any 'columns()'.

Here's what I have so far, but the filter seems to be putting the filter criteria into multiple quotes i.e. 13/10/21 becomes ""13/10/21"".

Code: Select all

Sub FilterOnOff2()

' Ensures data gets pasted into the correct rows
' by removing any filters then re-applying
    Sheets("Combined").Select
    Dim FltrDte As Date
    
    Range("A6").Select
    Selection.AutoFilter
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("A7").Select

    FltrDte = Range("D1").Value
       
    ActiveSheet.Range("$A$6:$AF$3401").AutoFilter Field:=2, Operator:= _
        xlFilterValues, Criteria2:=Array(2, FltrDte)
    
End Sub
Any ideas how best to do this?
N.B. FltrDte is a simple MAX calc. Did try .value & .string too.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel filter by latest date

Post by HansV »

Try this:

Code: Select all

Sub FilterOnOff2()
' Ensures data gets pasted into the correct rows
' by removing any filters then re-applying
    Dim FltrDte As Date

    Sheets("Combined").Select
    FltrDte = Range("D1").Value
    ActiveSheet.Range("$A$6:$AF$3401").AutoFilter _
        Field:=2, Criteria1:="=" & Format(FltrDte, "dd\/mm\/yy")
End Sub
Best wishes,
Hans

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Re: Excel filter by latest date

Post by Lost Paul »

Thank you.

Closer; the criteria is now "03/10/21" (i.e. has single set of quotes).
So still produces zero records.

Would it work better for the code to ascertain the latest date, rather than the MAX in D1?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel filter by latest date

Post by HansV »

What is the number format of the dates in column B?
Best wishes,
Hans

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Re: Excel filter by latest date

Post by Lost Paul »

dd/mm/yyyy

No times. Not text.
e.g. in number format, 03/10/21 = 44472

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel filter by latest date

Post by HansV »

I'm afraid I don't understand why the code doesn't work. Could you attach a small sample workbook with dummy data?
Best wishes,
Hans

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Re: Excel filter by latest date

Post by Lost Paul »

Here's a tester.
Tester.xlsm
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel filter by latest date

Post by HansV »

Does this work?

Code: Select all

Sub TopDate()
' Ensures data gets pasted into the correct rows
' by removing any filters then re-applying
    Dim FltrDte As Date
    Sheets("combined").Visible = True
    Sheets("Combined").Select
    FltrDte = Range("D1").Value
    Range("A6:AF3401").AutoFilter Field:=2, Criteria1:="=" & Format(FltrDte, Range("B7").NumberFormat)
End Sub
Best wishes,
Hans

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Re: Excel filter by latest date

Post by Lost Paul »

Yay!

That's the ticket.
Thank you.
:cheers: