CODEWITHSUNDEEP
vbaexcel
SBozhko
SBozhko

Reputation: 315

VBA EXCEL - Find the oldest date in a column

Currently working on a function, and as part of it i need to find the oldest date in a column in VBA. Have spent a while looking for solutions but cant find much. I found one line however it doesn't actually work. Ill post the code snippet I have but any suggestions or solutions would be greatly appreciated!

Public Function SetSOWDates() As String

    Dim wsCRC As Worksheet
    Set wsCRC = Worksheets("CRC")

    Dim LastRowInCRC As Long

    LastRowInCRC = CRC.LastRowInCRC()

    LatestDate = Format(wsCRC.Max(Range("K8:K" & LastRowInCRC)), "dd/mm/yyyy")

    Debug.Print LatestDate

End Function

Upvotes: 4

Views: 9192

Answers (2)

user4039065
user4039065

Reputation:

You are mixing up worksheetfunction calls with the worksheet and trying to stuff text-that-looks-like-a-date into a long integer.

Public Function SetSOWDates() As String

    Dim wsCRC As Worksheet
    Dim LastRowInCRC As Long

    Set wsCRC = Worksheets("CRC")

    LastRowInCRC = wsCRC.LastRowInCRC()

    LatestDate = application.Min(wsCRC.Range("K8:K" & LastRowInCRC))

    Debug.Print Format(LatestDate, "dd/mm/yyyy")

End Function

There was also a typo in CRC.LastRowInCRC() instead of wsCRC.LastRowInCRC().

Upvotes: 0

Rory
Rory

Reputation: 34045

You want Min, not Max for the oldest date, and it's a member of WorksheetFunction rather than the Worksheet:

LatestDate = Format(Worksheetfunction.Min(wsCRC.Range("K8:K" & LastRowInCRC)), "dd/mm/yyyy")

Upvotes: 5

Related Questions