Table of Contents
VBA makes it easy to find the minimum value in a range by using the WorksheetFunction.Min() function. This function takes a range as an argument and returns the minimum value of the range. To use this function, simply pass the range to the function as an argument and the function will return the smallest value in the range. Additionally, the WorksheetFunction.Min() function also supports the use of criteria to find the minimum value within a range that meets certain criteria.
You can use the following basic syntax to calculate the minimum value in a range using VBA:
Sub MinValue()
Range("D2") = WorksheetFunction.Min(Range("B2:B11"))
End Sub
This particular example calculates the minimum value in the range B2:B11 and assigns the result to cell D2.
If you would instead like to display the minimum value in a message box, you can use the following syntax:
Sub MinValue()
'Create variable to store min value
Dim minValue As Single
'Calculate min value in range
minValue = WorksheetFunction.Min(Range("B2:B11"))
'Display the result
MsgBox "Min Value in Range: " & minValue
End Sub
The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains information about various basketball players:
Related:
Example 1: Calculate Minimum Value of Range Using VBA and Display Results in Cell
Suppose we would like to calculate the minimum value in the points column and output the results in a specific cell.
We can create the following macro to do so:
Sub MinValue()
Range("D2") = WorksheetFunction.Min(Range("B2:B11"))
End Sub
When we run this macro, we receive the following output:
Notice that cell D2 contains a value of 10.
This tells us that the minimum value in the points column is 10.
Example 2: Calculate Minimum Value of Range Using VBA and Display Results in Message Box
We can create the following macro to do so:
Sub MinValue()
'Create variable to store min value
Dim minValue As Single
'Calculate min value in range
minValue = WorksheetFunction.Min(Range("B2:B11"))
'Display the result
MsgBox "Min Value in Range: " & minValue
End Sub
When we run this macro, we receive the following output:
The message box tells us that the minimum value in the range B2:B11 is 10.
Note that in this example we calculated the minimum value in the range B2:B11.
However, if you’d like to instead calculate the minimum value in an entire column you could type B:B instead.
This will calculate the minimum value in all of column B.