VBA - 为两列中的每一列找到最大值和最小值

这将显示与最小和最大价值一个消息框，一个消息框，假设两列A和B.

Sub MinMax() 
    ' Count the number of rows in the first column 
    ' Assuming that number of rows in second column equals those in the first column 
    HowFar = WorksheetFunction.CountA(Range("A:A")) 
    ' Declare an array to store the minimums 
    Dim Arr() As Double 
    ReDim Arr(HowFar - 2) 
    Dim Index As Integer 
    Index = 0 
    ' Loop through the first and second columns and store the minimums for each row in the array 
    Dim i As Integer 
    For i = 2 To HowFar 
     Minimum = Application.WorksheetFunction.Min(Range("A" & i & ":B" & i)) 
     Arr(Index) = Minimum 
     Index = Index + 1 
    Next i 
    ' Get the minimum value in the array 
    Min = Application.WorksheetFunction.Min(Arr) 
    ' Get the maximum value in the array 
    Max = Application.WorksheetFunction.Max(Arr) 
    ' MsgBox the two values 
    MsgBox ("Minimum = " & Min) 
    MsgBox ("Maximum = " & Max) 
End Sub