Minimum value in a column

A

AlanDavidson

Could someone tell me how to use VBA to find the minimum value in
column containing integers.

Regards,
Ala
 
C

Chip Pearson

Alan,

Try something like the following:

Dim Res As Long
Res = Application.WorksheetFunction.Min(Range("A1:A10"))
Debug.Print Res


Change the reference A1:A10 to your desired range.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"AlanDavidson >" <<[email protected]>
wrote in message
news:[email protected]...
 
A

Alan

Thanks Chip. That worked fine.

Regards,
Alan

-----Original Message-----
Alan,

Try something like the following:

Dim Res As Long
Res = Application.WorksheetFunction.Min(Range("A1:A10"))
Debug.Print Res


Change the reference A1:A10 to your desired range.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"AlanDavidson >" <<AlanDavidson.168sv7@excelforum- nospam.com>
wrote in message
minimum value
in a


.
Click to expand...
 
J

Johnny Meredith

Here are two ways to do it. There must be many more:

Name the range containing the intergers "Foo"

Sub foo()
Dim rng As Range
Dim cell As Object
Dim i As Integer
Set rng = Range("Foo")
i = rng(1)
With rng
For Each cell In rng.Cells
If cell.Value < i Then
i = cell.Value
End If
Next
End With
Debug.Print i
Set rng = Nothing
End Sub

This simply assigns the first value in range "Foo" to i, then tests the values
in each cell in range "Foo". If that value is less than i, i gets reassigned,
otherwise i remains the same and the loop commences.

Here's another way, using the evaulate method:

Sub foo2()
Dim rng As Range
Dim i As Integer
Set rng = Range("foo")
i = Evaluate("Min(" & rng.Address & ")")
Debug.Print i
End Sub

Here, you borrow Excel's built-in Min function using the Evaluate method.
I'm not sure which method is preferrable, but in the interests of typing less,
maybe the second approach is best.

HTH,
Johnny

Other readers: Do you know how to convert the Evaluate method above to the
shorthand version using square brackets? I couldn't get it to work.
 
Top