Autofilter latest event date

[JOEYSCLEE]

Hi, there
Would you please to write the macro for filtering latest record with the header Event Date. Kindly note that the event date is text format instead of date format.

Remark: Enclosed the attachment with 2 tabs. I would like the result as per the file.

[mdmackillop]

Give this a try. I can't test it properly because of the crazy American date convention!

Sub Test()
    LR = Cells(Rows.Count, 7).End(xlUp).Row
    Columns("$G:$G").AutoFilter
    Range("$G$4:$G" & LR).AutoFilter Field:=1, Criteria1:="=" & LastDate(7) & "*"
End Sub


Function LastDate(Col) As String
    Dim r As Range, dt As Date, ld As Date
    Set r = Range(Cells(5, Col), Cells(Rows.Count, Col).End(xlUp))
    ld = 0
    For Each cel In r
        dt = DateValue(Left(cel, 10))
        If dt > ld Then ld = dt
    Next
    LastDate = CStr(ld)
End Function

[p45cal]

Using DateValue is the most straightforward solution, but if that doesn't work for you then we can be explicit about the dates by changing:
dt = DateValue(Left(cel, 10))
to:
dt = DateSerial(Mid(cel, 7, 4), Left(cel, 2), Mid(cel, 4, 2))


and:
LastDate = CStr(ld)
to:
LastDate = Format(ld, "mm/dd/yyyy")

[JOEYSCLEE]

Hi, mdmackillop....Thank you for quick response!! However, it's not workable.

Hi, p45cal.............Thank you for reviewing!!! After changing code, it is workable now.

Sub Test()
    LR = Cells(Rows.Count, 7).End(xlUp).Row
    Columns("$G:$G").AutoFilter
    Range("$G$4:$G" & LR).AutoFilter Field:=1, Criteria1:="=" & LastDate(7) & "*"
End Sub


Function LastDate(Col) As String
    Dim r As Range, dt As Date, ld As Date
    Set r = Range(Cells(5, Col), Cells(Rows.Count, Col).End(xlUp))
    ld = 0
    For Each cel In r
        dt = DateSerial(Mid(cel, 7, 4), Left(cel, 2), Mid(cel, 4, 2))
        If dt > ld Then ld = dt
    Next
    LastDate = Format(ld, "mm/dd/yyyy")
End Function