Autofilter latest event date

[JOEYSCLEE]

Hi, there
Would you please :help 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