VBA get max date from the format YYYY-DD-MM (like 2023-10-02)

Question

In VBA I want to extract the max date (or max value) from a range, but I don't want to change the format of the range. Now it looks like below:

Date:
2023-10-02
2023-10-03
2023-10-04
2023-10-05
2023-10-06
2023-10-07

But the normal max function is not working in VBA...

There are also empty cells in this column, so it would be good to not consider these ones.

Thank you in advance and best regards!

Answer 1

This function can be used both in a worksheet and in VBA

Option Explicit

Function GetMaxDate(rng As Range) As Variant
    Dim cur_date As Date, arr As Variant, d As Variant
    arr = rng ' get all the data into an array to improve performance (one operation of reading data from the sheet)
    For Each d In arr
        If IsDate(d) Then ' we check whether the next value can be a date
            cur_date = CDate(d) '
            If GetMaxDate < cur_date Then GetMaxDate = cur_date ' select max date
        End If
    Next
    If Not IsEmpty(GetMaxDate) Then
        GetMaxDate = Format(GetMaxDate, "yyyy-mm-dd")
    Else
        GetMaxDate = "#NODATE"
    End If
End Function

Answer 2

You can utilize the worksheet function Max to obtain the maximum date, and the return value will be in the form of a double.

In cases where A1 might be empty, using Range("A1").End(xlDown) will locate the next non-blank cell. If column A follows the same format throughout, then Range("A1").NumberFormatLocal should suffice.

Debug.Print Format(Application.WorksheetFunction.Max(Range("A:A")), Range("A1").End(xlDown).NumberFormatLocal)

If you have a clear understanding of the expected output format, it can lead to simpler code.

Debug.Print Format(Application.WorksheetFunction.Max(Range("A:A")), "yyyy-mm-dd")