Excel VBA Minimum: Find the Smallest Value

VBA & macros · Updated November 21, 2026

In Excel VBA, use Application.WorksheetFunction.Min to return the smallest value from a range or a set of numbers; VBA does not have a built-in function named Min. For example, with numbers in A2:A20, assign Application.WorksheetFunction.Min(Range("A2:A20")) to a variable. The method follows Excel’s MIN rules, so blanks and text in referenced cells are ignored, while an error in the range needs handling.

What VBA’s minimum function does

Excel’s worksheet function MIN returns the lowest numeric value in its arguments. In VBA, you can call that worksheet function through the Excel object model. The usual form is Application.WorksheetFunction.Min, followed by one or more arguments, such as a range or individual numbers. The result is the minimum, not the position of the cell that contains it.

For example, suppose A2:A6 contains 12, 4, a blank cell, -3, and 7. If the final value is a number stored as text, Excel’s MIN ignores it when it is passed as part of a cell reference. With that layout, the minimum of A2:A6 is -3. If you want the location of that value as well, you need an additional lookup or search step.

In a worksheet cell, the equivalent is the formula =MIN(A2:A6). In VBA, the method name is capitalized as shown, but VBA is not case-sensitive. The key distinction is that Min is an Excel worksheet function accessed from VBA, not a standalone built-in VBA function.

Basic VBA example

The following macro calculates a minimum from a fixed range and displays it. It checks that the range contains at least one numeric value before calling MIN, because an empty or all-text range can otherwise produce a misleading zero result.

Sub ShowMinimum()
    Dim ws As Worksheet
    Dim values As Range
    Dim smallest As Double

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set values = ws.Range("A2:A20")

    If Application.WorksheetFunction.Count(values) = 0 Then
        MsgBox "The range contains no numeric values."
        Exit Sub
    End If

    On Error GoTo HandleError
    smallest = Application.WorksheetFunction.Min(values)
    MsgBox "Minimum value: " & smallest
    Exit Sub

HandleError:
    MsgBox "The minimum could not be calculated: " & Err.Description
End Sub

Change Sheet1 and A2:A20 to match the workbook. ThisWorkbook means the workbook containing the code; use ActiveWorkbook only when you deliberately want to target whichever workbook is active. The error handler catches calculation failures, including a range containing an Excel error value.

Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. On Windows, open the Visual Basic Editor with Alt+F11; on Mac, Option+F11 is the common shortcut, though some keyboards require Fn as well. Excel for the web can open workbooks containing macros, but it cannot run or edit VBA code.

What counts as a value

MIN’s treatment of cells is important when a range contains mixed content. When a cell reference is supplied, MIN ignores empty cells, text, and logical values in cells. A number stored as text is also ignored rather than treated as a number. Numeric cells, including negative numbers and zero, are considered. If a referenced cell contains an error such as #N/A or #VALUE!, the calculation can fail instead of returning the smallest valid number.

Dates and times stored as genuine Excel values are numbers under the surface, so MIN can include them. Excel stores dates as serial numbers; the smallest serial in a range of dates corresponds to the earliest date. If you show the result in a cell formatted as General, you may see a serial number rather than a date. Apply a date format when that is the desired display.

There is a small but useful distinction between values in referenced cells and values supplied directly as arguments. Worksheet MIN can evaluate numbers supplied directly, and its treatment of text or logical arguments can differ from how it treats those values inside a referenced range. If consistent handling matters, pass a clearly defined range and check the source data rather than relying on mixed argument types.

Handling empty ranges and errors

MIN returns zero when it receives no numeric values. That result can be mistaken for a real minimum, particularly when zero is a valid value in the dataset. The example macro uses WorksheetFunction.Count first; Count counts numeric cells in a range and returns zero when there are none. This check distinguishes “no numeric observations” from a genuine minimum of zero.

A separate issue is an error cell. Count does not make an error disappear from the later MIN calculation. The macro’s error handler is therefore still useful: it prevents an unhandled VBA runtime error and lets the user know that the calculation did not complete. For production code, you may want the handler to log the range address or report which cells need correction rather than simply showing the error description.

Do not replace missing values with zero unless zero is the correct meaning for your task. If a dataset has no valid numbers, treating its minimum as zero changes the result and can distort later summaries. Decide whether to skip invalid entries, report that there is no result, or stop and ask for the source data to be fixed.

Using Min with more than one range or value

The method can accept multiple arguments. For instance, you can compare two ranges or combine a range with a numeric threshold. In VBA, an example call is Application.WorksheetFunction.Min(ws.Range("A2:A10"), ws.Range("C2:C10"), 0). This returns the smallest number among the eligible values in those arguments, including zero. Make sure the ranges refer to the intended worksheet; qualifying them with a worksheet variable avoids accidentally using a range from the active sheet.

You can also put the result into a worksheet cell instead of displaying it. With the destination cell in D2 and the data in A2:A20 on the same worksheet, VBA can assign the result of the Min call to Range("D2").Value. If the data is likely to change and the user should see a live result, entering a worksheet formula may be simpler than rerunning a macro. If the calculation is part of a larger automated process, VBA can calculate and use the value immediately without writing a formula.

Choosing between WorksheetFunction.Min and a loop

For ordinary numeric data, Application.WorksheetFunction.Min is concise and uses Excel’s established MIN behavior. A custom loop is more appropriate when the rules differ from Excel’s defaults—for example, when numeric text should count, certain rows should be excluded, or the code needs to track which cell supplied the minimum. A loop also makes each inclusion rule visible, but it requires more code and careful handling of blanks and errors.

This example scans a range, skips errors and empty cells, and accepts values that VBA considers numeric. Unlike worksheet MIN on a cell reference, that rule can include numeric text such as the text value “7”. The code tracks whether it has found a candidate so that a real zero or a negative value is handled correctly.

Function MinimumIncludingNumericText(values As Range) As Variant
    Dim cell As Range
    Dim candidate As Double
    Dim smallest As Double
    Dim found As Boolean

    For Each cell In values.Cells
        If Not IsError(cell.Value2) Then
            If Len(cell.Value2) > 0 Then
                If IsNumeric(cell.Value2) Then
                    candidate = CDbl(cell.Value2)
                    If Not found Or candidate < smallest Then
                        smallest = candidate
                        found = True
                    End If
                End If
            End If
        End If
    Next cell

    If found Then
        MinimumIncludingNumericText = smallest
    Else
        MinimumIncludingNumericText = CVErr(xlErrNum)
    End If
End Function

This is a user-defined function (UDF), so it can be called from a worksheet after it is placed in a standard VBA module. With the input range in A2:A6, enter =MinimumIncludingNumericText(A2:A6) in a worksheet cell. If no usable value is found, it returns an Excel #NUM! error rather than silently claiming that zero is the minimum. Be aware that this custom function has intentionally different rules from Excel’s MIN, so document those rules for anyone using the workbook.

Common mistakes and practical checks

  • Calling Min as if it were native VBA: Min(x, y) by itself is not the usual Excel VBA call. Use Application.WorksheetFunction.Min or write a custom comparison routine.
  • Assuming a zero result means a zero was present: MIN also returns zero when there are no numeric values. Check the numeric count if that distinction matters.
  • Overlooking number-shaped text: A cell that displays “7” may contain text, so it can be ignored by MIN. Convert the source data to numbers or use a custom rule if text numerals should count.
  • Leaving out the worksheet reference: An unqualified Range("A2:A20") refers to the active sheet. Qualify it with a worksheet variable in code that may run while another sheet is active.
  • Expecting Ctrl+Z to reverse a macro: Macro actions generally clear the normal undo history. Save a backup before running code that changes workbook contents.

Frequently Asked Questions

Is there a Min function in VBA?
VBA has no general built-in Min function. In Excel VBA, call Excel’s worksheet function with Application.WorksheetFunction.Min, or create your own function or loop when you need custom rules.

How do I find the smallest value in a column?
Pass the relevant cells in that column to Application.WorksheetFunction.Min, such as Application.WorksheetFunction.Min(ws.Range("B2:B100")). Use a bounded range that excludes headings and unrelated totals, and check for numeric values if the range may be empty.

Does VBA Min ignore blank cells?
Yes. Excel’s MIN ignores blank cells and text in a referenced range. It also ignores logical values in referenced cells. Numeric text is not the same as a numeric cell value and is ignored in that range-based calculation.

Why does Min return zero when the range looks empty?
When there are no numeric values in its arguments, MIN returns zero. The cells may be blank, contain text, or contain numbers stored as text. Check the range’s values and use WorksheetFunction.Count to determine whether it contains any numeric cells.

Can I use this VBA code in Excel for the web?
No. Excel for the web does not run VBA macros or provide the desktop Visual Basic Editor. Use Excel for Microsoft 365 on Windows or Mac to run VBA; worksheet formulas such as MIN can still be used in the web app.

Related

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.