HEELPBOOK - Excel - Finding the Maximum Value in Each Column in a Range ###########################

The following 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 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:

http://heelpbook.altervista.org/wp-content/uploads/2012/04/max-each-column.jpg

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

############ ARTICLE INFO #############

Article Month: April
Article Date: 01/04/2012
Permalink: http://heelpbook.altervista.org/2012/excel-finding-the-maximum-value-in-each-column-in-a-range/
Source: http://www.automateexcel.com/2008/10/16/finding-the-maximum-value-in-each-column-in-a-range/
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook


Images: 

- http://heelpbook.altervista.org/wp-content/uploads/2012/04/max-each-column.jpg