Find the Maximum and Minimum Value in the Range in VBA

Find-Maximum-and-Minimum-Value-in-Range-in-VBA Download File

In terms of the things that we can work on, we can observe Visual Basic for Applications (VBA) as an extension of Excel. All the things that are at our disposal in Excel, are available in VBA as well.

In the example below, we will show how to find the minimum and maximum values in our range. We will encompass two scenarios: if the range is in our Worksheet, or if it is in our VBA code.

Find the Maximum and Minimum Value of a Range in the Worksheet

For our example, we will use a table of different salespersons and achieved sales in the period from 2012 to 2019:

After this, we need to open VBA, and we do that by clicking ALT + F11 on our keyboard. After that, we go to the Insert tab and choose Module:

C:\Users\notebook\Documents\Get the Size of the Range in Excel\insert-module.png.PNG

When we have our Module opened, we will insert the following formula to get the largest number (maximum) in our range (A3:I8):

Sub MaximumInRange()
    Dim r As Range
    Dim m As Double
    Set r = Range("A3:I8")
    m = Application.WorksheetFunction.Max(r)
    MsgBox "The largest number in our range Is " & m
End Sub

Sub MaximumInRange()

Dim r As Range

Dim m As Double

Set r = Range("A3:I8")

m = Application.WorksheetFunction.Max(r)

MsgBox "The largest number in our range Is " & m

End Sub

This formula declares two variables, r, and m. Then it sets variable r to be equal to our range. M variable will be equal to the maximum value of our range, and the message box will be used to present this value to a user.

When we execute the code by pressing F5 on our keyboard, this is what we get:

To get the lowest value, all we have to do is copy/paste the code and change Max in our formula with Min, and the text in the message box. This will be our formula:

Sub MinimumInRange()
    Dim r As Range
    Dim m As Double
    Set r = Range("A3:I8")
    m = Application.WorksheetFunction.Min(r)
    MsgBox "The smallest number in our range Is " & m
End Sub

Sub MinimumInRange()

Dim r As Range

Dim m As Double

Set r = Range("A3:I8")

m = Application.WorksheetFunction.Min(r)

MsgBox "The smallest number in our range Is " & m

End Sub

When we execute this code (by pressing F5 while located in the formula in the VBA) this is the message that the user can see:

Find the Maximum and Minimum Value of a Range in VBA

We use the same function to determine the largest and smallest value if the range is located in VBA, rather than in the worksheet. To show this, we will create the following formula:

Sub MaximumAndMinimumInVBA()
    Dim a, b, c, d, max, min As Integer
    a = 70
    b = 86
    c = 28
    d = 43
    max = WorksheetFunction.max(a, b, c, d)
    min = WorksheetFunction.min(a, b, c, d)
    MsgBox "Largest number is " & max
    MsgBox "Smallest number is " & min
End Sub

Sub MaximumAndMinimumInVBA()

Dim a, b, c, d, max, min As Integer

a = 70

b = 86

c = 28

d = 43

max = WorksheetFunction.max(a, b, c, d)

min = WorksheetFunction.min(a, b, c, d)

MsgBox "Largest number is " & max

MsgBox "Smallest number is " & min

End Sub

In this formula, we have created six variables, four to hold various numbers, one to show the maximal, and one to show the minimal value of our numbers. We use Worksheet.Function with min and max, add our numbers, and then show the values through message boxes. This is what our code looks like in the VBA:

When we execute the code (F5 on our keyboard) we will get two messages (one for the largest, and one for the smallest number in our range):

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 1,600