VBA to find most recent date

[Tanyaann1995]

Hi,

I have a code below that finds the most recent date in a list and displays it.

VBA Code:

Sub dateshow()

Dim max As Date
max = Application.WorksheetFunction.max(Columns("A"))
MsgBox CDate(max)

End Sub

My list of dates is below. But, when i try the above code, the message box displays 12:00:00 AM. Please advise why.

16-Jul-2020

23-Jul-2020

23-Jul-2020

23-Jul-2020

08-Dec-2020

07-Jan-2021

 

[Marc L]

Hi,​

​

maybe your dates are not Date data type … To avoid a NES the better is to link your workbook on a files host website …​

 

[Tanyaann1995]

Hi,​

​

maybe your dates are not Date data type … To avoid a NES the better is to link your workbook on a files host website …​

Hi,

I didn't understand your reply. Pls explain further. Im not able to figure this out.

 

[MARK858]

Assuming that your data starts in A2, in a blank column in row 2 put the formula

=ISNUMBER(A2)

and drag down.
Are all the results TRUE?

 

[steve the fish]

You could try this which shouldnt mind if the dates are textual.

Sub dateshow()

Dim arr, myMax As Date, lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
arr = Range("A1:A" & lr)

For i = LBound(arr) To UBound(arr)
    If IsDate(arr(i, 1)) Then
        If arr(i, 1) > myMax Then
            myMax = arr(i, 1)
        End If
    End If
Next

MsgBox CDate(myMax)

End Sub

 

[Tanyaann1995]

You could try this which shouldnt mind if the dates are textual.

Sub dateshow()

Dim arr, myMax As Date, lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
arr = Range("A1:A" & lr)

For i = LBound(arr) To UBound(arr)
    If IsDate(arr(i, 1)) Then
        If arr(i, 1) > myMax Then
            myMax = arr(i, 1)
        End If
    End If
Next

MsgBox CDate(myMax)

End Sub

Thank you. this worked