Excel – Finding the Maximum Value in Each Column in a Range

Send Us a Sign! (Contact Us!)

--> (Word)	--> (PDF)	--> (Epub)	--> (Text)
--> (XML)	--> (ODT)	--> (XPS)	--> (Excel)

Show More Info

This article has been read [post_view] times. This article has been read [post_view time="day"] times today. This article has been read [post_view time="month"] times this month. This article has been read [post_view time="year"] times this year. This article has 243 total words. (Including header) [tweet]

The following [gs function] will return the Maximum Value in each Column in a Range:

Function Max_Each_Column(Data_Range As Range) As Variant
    Dim TempArray() As Double, i As Long
        If Data_Range Is Nothing Then Exit Function
        With Data_Range
        ReDim TempArray(1 To .Columns.Count)
        For i = 1 To .Columns.Count
            TempArray(i) = Application.Max(.Columns(i))
        Next
    End With
        Max_Each_Column = TempArray
End Function

We can use a [gs subroutine] like the following to display the results:

Private Sub CommandButton1_Click()
Dim Answer As Variant
Dim No_of_Cols As Integer
Dim i As Integer
No_of_Cols = Range("B5:G27").Columns.Count
ReDim Answer(No_of_Cols)
Answer = Max_Each_Column(Sheets("Sheet1").Range("B5:g27"))

For i = 1 To No_of_Cols
MsgBox Answer(i)
Next i
End Sub

So:

Will return 990,907, 992, 976 ,988 and 873 for each of the above columns.

SOURCE	LINK (Automateexcel.com)	LANGUAGE	ENGLISH