max & min of a:a ect........

[jdavis9]

How do i return the max and min values of a column to vb?

the equiv of =max(a:a) or =min(a:a)

Thanks

 

[Mudface]

Try the following, change the MsgBox bit to YourVariable = ... etc: -
<pre>
MsgBox Application.WorksheetFunction.Max(Range("A:A"))
MsgBox Application.WorksheetFunction.Min(Range("A:A"))
</pre>

 

[dk]

Hi,

You can use Excel's WorksheetFunction object to use many worksheet functions in your VBA code. For example:-

Sub GetMaxAndMinValues()
Dim vMax, vMin

vMax = WorksheetFunction.Max(Range("A:A"))
vMin = WorksheetFunction.Min(Range("B:B"))

MsgBox "Maximum value in column A = " & vMax & vbCrLf & _
        "Minimum value in column B = " & vMin, vbInformation, "Max and Min"

End Sub

To see a comprehensive list of worksheet functions which work in VBA see List Of Worksheet Functions Available to Visual Basic,

 

[jdavis9]

Thanks for the help......... worked like a champ.............