Excel VBA: Find the Minimum Value in a Range or Array

To find a range’s minimum in Excel VBA, use Application.WorksheetFunction.Min, for example, Application.WorksheetFunction.Min(Range("B2:B20")). For a macro you can reuse, assign the range to a variable, check that it contains a number, then store or display the result.

Find the minimum in a worksheet range

The MIN function is usually the simplest choice when the values are in worksheet cells. This complete macro reads B2:B20 on Sheet1 and writes the smallest numeric value to D2. It checks for numeric entries first because MIN returns 0 when its inputs contain no numbers; zero could otherwise be mistaken for a genuine minimum.

Before running any macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z.

Sub FindMinimumInRange()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim minValue As Double
    Dim numberCount As Double

    ' Read the source range on the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceRange = ws.Range("B2:B20")

    ' Stop if the range contains no numeric values.
    numberCount = Application.WorksheetFunction.Count(sourceRange)
    If numberCount = 0 Then
        MsgBox "No numeric values were found in the source range."
        Exit Sub
    End If

    ' Calculate the minimum and write it to D2.
    minValue = Application.WorksheetFunction.Min(sourceRange)
    ws.Range("D2").Value = minValue
End Sub

Change the worksheet name and addresses to match your workbook. Because the code uses ThisWorkbook, it refers to the workbook where the macro is stored, not necessarily whichever workbook happens to be active. That can prevent a macro from reading the wrong file when several workbooks are open.

What MIN counts and ignores

For a cell reference, Excel’s MIN function considers numeric values. It ignores blank cells, text, and logical values such as TRUE or FALSE stored in cells. A numeric zero is still a number, so it can be the minimum. Dates and times are stored as numbers in Excel, so MIN can return the earliest date or time in a range; format the result as a date or time if you want it displayed that way.

Errors are different from ordinary text. A range containing an error such as #N/A can cause the MIN calculation to return an error. Decide how the macro should handle those cells: stop and report the error, or deliberately skip them. Silently treating an error as a normal value can conceal a problem in the source data.

MIN also does not mean “smallest visible value.” Filtering or hiding rows does not, by itself, make MIN ignore them. If the requirement is specifically the smallest value in visible rows, that is a different task and needs a visibility-aware approach. Be clear about whether hidden and filtered-out records should count before choosing a method.

WorksheetFunction.Min versus Application.Min

Both forms call Excel’s MIN calculation: Application.WorksheetFunction.Min(sourceRange) and Application.Min(sourceRange). For clean input, they ordinarily produce the same numeric result. The difference matters when the calculation returns an Excel error. Calling a function through WorksheetFunction can raise a VBA runtime error, while Application.Min can return a Variant containing an error value that your code can test with IsError.

For example, use Application.Min when you want to inspect the result before using it, rather than allowing an unexpected worksheet error to interrupt the macro. A result variable should then be declared as Variant, since it may hold either a number or an error value. Neither form automatically tells you which input cell caused an error; if you need to identify the bad cell, inspect the source range separately.

For straightforward calculations with known, clean values, WorksheetFunction.Min is clear and convenient. For uncertain inputs where error handling is part of the plan, Application.Min offers a result that can be checked. Do not assume that choosing one form changes which cells MIN considers numeric.

Find the minimum in an array

You can also pass a VBA array to MIN. This is useful when values have already been read into memory or calculated by earlier code. For example, the next macro copies B2:B20 into a Variant array, checks for numeric data, and displays the minimum. Reading a block into an array can also be more efficient than repeatedly accessing worksheet cells in a large loop.

Save a copy before running this macro, just as you would before running any VBA code.

Sub FindMinimumInArray()
    Dim ws As Worksheet
    Dim values As Variant
    Dim minValue As Double
    Dim numberCount As Double

    ' Load the worksheet values into a two-dimensional array.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    values = ws.Range("B2:B20").Value2

    ' Check that the array contains at least one number.
    numberCount = Application.WorksheetFunction.Count(values)
    If numberCount = 0 Then
        MsgBox "No numeric values were found."
        Exit Sub
    End If

    ' Calculate and display the minimum.
    minValue = Application.WorksheetFunction.Min(values)
    MsgBox "Minimum value: " & minValue
End Sub

A multi-cell range’s Value2 property produces a two-dimensional array, even when the range has only one column. MIN can handle that array. If your macro sometimes reads a single cell, remember that a one-cell range’s value is a scalar rather than a two-dimensional array; the direct range version avoids having to account for that difference.

Return the cell that contains the minimum

Sometimes the number alone is not enough: you may need the address of the cell holding it. The following example scans a range, keeps the lowest numeric value seen so far, and records the matching cell. It skips blanks, text, logical values, and error values, consistent with the goal of comparing numeric cell values. If your data contains errors, decide whether skipping them is appropriate for your report.

When two cells share the same minimum, this loop returns the first one in the range’s normal top-to-bottom, left-to-right order. Save a copy before running the macro.

Sub FindMinimumCell()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim cell As Range
    Dim cellValue As Variant
    Dim minValue As Double
    Dim minCell As Range

    ' Set the range to search.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceRange = ws.Range("B2:B20")

    ' Compare numeric values and remember the first minimum.
    For Each cell In sourceRange.Cells
        cellValue = cell.Value2
        If Not IsError(cellValue) Then
            If VarType(cellValue) = vbDouble Then
                If minCell Is Nothing Then
                    minValue = cellValue
                    Set minCell = cell
                ElseIf cellValue < minValue Then
                    minValue = cellValue
                    Set minCell = cell
                End If
            End If
        End If
    Next cell

    ' Report whether a numeric value was found.
    If minCell Is Nothing Then
        MsgBox "No numeric values were found."
    Else
        MsgBox "Minimum: " & minValue & " in " & minCell.Address(False, False)
    End If
End Sub

In this HTML code block, < and & are displayed as the VBA comparison operator and concatenation operator. The address is relative, such as B7, rather than including the sheet name. If you need a fully qualified reference, include the worksheet name when building the output.

Handle empty ranges, errors, and data types

An empty range and a range with no numeric entries are not quite the same situation. A range may contain labels or blanks but no numbers; MIN then returns zero. The Count check in the examples counts numeric cells, so the macro can report “no numeric values” instead of presenting that zero as if it came from the data.

For a range containing both numbers and errors, a call through WorksheetFunction.Min may stop with a runtime error. If you want to skip errors, use an explicit scan like the address example, which tests IsError before comparing a value. If you instead want errors to stop the process, use error handling or check values and report the offending cell. Pick one policy and make it visible to whoever relies on the result.

Be cautious when replacing the numeric-type check in the loop with IsNumeric. That test can accept numeric text, such as a cell containing the text “12”, even though MIN generally ignores text in a referenced range. If treating numeric text as a number is intentional, convert it deliberately and define how to handle spaces, decimal separators, and invalid text. Otherwise, use a method that matches MIN’s treatment of worksheet cell values.

Put the macro in the right place and run it

In Excel for Microsoft 365 on Windows, open the workbook and choose Developer > Visual Basic. In the Visual Basic Editor, choose Insert > Module, paste the code into the module, and return to Excel. If the Developer tab is missing, enable it through File > Options > Customize Ribbon, then select Developer. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro.

To run a macro on Windows, press Alt+F8, select its name, and choose Run. In Excel for Mac, the macro dialog shortcut is commonly Option+F8, although the exact key combination can depend on the keyboard and macOS settings; you can also use the Mac Excel interface to open the macro list. Excel for the web can open and edit many workbooks, but it cannot run VBA macros. Open the file in the desktop Excel app to run this code.

Only enable macros in workbooks you trust. A macro that calculates a minimum is simple, but VBA can also change or delete workbook content. Keep a backup and review code before running it, especially if it came from an unfamiliar source.

Choose the right approach

Use WorksheetFunction.Min when you want the minimum value from a known range or array and the input is expected to be valid.
Use Application.Min when you want to check whether the returned result is an error before proceeding.
Loop through cells when you need the address of the minimum, custom tie handling, or an explicit rule for skipping errors and nonnumeric values.
Use a worksheet formula when the result should update automatically as users edit the data and a macro is unnecessary.

For example, with numbers in B2:B20, a worksheet formula entered in D2 can be =MIN(B2:B20). It recalculates when the referenced values change, while a macro only recalculates when it runs. The formula is often the better fit for a live display; VBA is useful when the minimum is one step in a larger automated process, such as generating a report or writing results to several places.

Frequently Asked Questions

What is the VBA code to find the smallest number in a range?
Use Application.WorksheetFunction.Min(Range("B2:B20")). In a macro, qualify the range with a worksheet and check that it contains numeric cells before relying on the result.

Does VBA MIN ignore blank cells and text?
When MIN receives a worksheet range reference, it ignores blank cells, text, and logical values in the cells. It includes numeric zero. Error values can cause the calculation to return an error.

Why does MIN return 0 when my range has no numbers?
MIN returns zero when it has no numeric values to evaluate. Use Application.WorksheetFunction.Count on the range first and handle a count of zero as a separate case.

How do I find the address of the minimum value?
Scan the cells, compare numeric values, and store the cell that currently holds the minimum. The address example above returns the first matching cell if the minimum occurs more than once.

Can Excel for the web run a VBA macro?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro; a worksheet MIN formula can still calculate in the web app.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.