excel - Max of substring in column

Question

Given that I have a column of values in the format

01.2020
12.2021
3.2019
02.2020
etc.

over a million rows or so, I want a VBA function to find the maximum value of the digits to the left of the period.

Something like

Option Explicit

Sub test()
    Dim r As Range, c As Range
    Dim max As Long, current As Long
    Dim s As String
    
    With ?r_2020
        Set r = .Range(.Range("D2"), .Range("D" & .Rows.Count).End(xlUp))
    End With
    
    For Each c In r
        current = CLng(Left(c, InStr(1, c, ".", vbBinaryCompare) - 1))
        If current > max Then max = current
    Next c
    
    Debug.Print max
End Sub

works, but I feel like there ought to be a simpler solution.

Can someone please give some input on whether they can find a simpler solution?

question from:https://stackoverflow.com/questions/66064144/max-of-substring-in-column

Answer 1

Formula solution

• formula in B1: =VALUE(LEFT(A1,FIND(".",A1)-1))
• in C1: =MAX(B:B)

VBA solution

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim LastRow As Long 'find last used row
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Dim ValuesToConvert() As Variant 'read data into array
ValuesToConvert = ws.Range("A1", "A" & LastRow).Value 'will throw an error if there is only data in A1 but no data below, make sure to cover that case

Dim iVal As Variant
For iVal = LBound(ValuesToConvert) To UBound(ValuesToConvert)
    'truncate after period (might need some error tests if period does not exist or cell is empty)
    ValuesToConvert(iVal, 1) = CLng(Left(ValuesToConvert(iVal, 1), InStr(1, ValuesToConvert(iVal, 1), ".") - 1))
Next iVal

'get maximum of array values
Debug.Print Application.WorksheetFunction.Max(ValuesToConvert)