Excel VBA: Find the Minimum Value in a Worksheet Range

In Excel VBA, use Application.WorksheetFunction.Min to get the smallest number in a range. For example, if the values are in A1:A10 on Sheet1, assign Application.WorksheetFunction.Min(Worksheets("Sheet1").Range("A1:A10")) to a variable; use the macro below to check for an empty numeric range and put the result in a worksheet cell.

What the Min function returns

Excel’s MIN function returns the lowest numeric value among its arguments. In VBA, Application.WorksheetFunction.Min makes that worksheet function available to your macro. You can pass it a Range object, so the calculation can refer to cells whose values change rather than numbers fixed in the code.

For example, if cells A1:A10 contain 12, 8, 15, and 3, along with blank cells, the minimum is 3. The function returns the value, not the address of the cell containing it. A cell showing the result can still have its own number format: if the minimum is a date serial number, for example, Excel may display that number as a date when the result cell is date-formatted.

If you only need a worksheet calculation and do not need VBA to perform an action, enter a formula instead. With the values in A1:A10, =MIN(A1:A10) returns the smallest numeric value and updates when the range changes. A macro is useful when the result is part of a larger process, such as reporting, copying, or responding to a button click.

Run a basic VBA macro

This example reads A1:A10 on Sheet1 and writes the minimum to B1. It checks whether the range contains any numeric cells before calculating, and catches a calculation error such as one caused by an error value in the range. Change the sheet name and range to match your workbook. B1 will be overwritten, so choose another output cell if it contains data you need.

Sub FindMinimumInRange()
    Dim ws As Worksheet
    Dim rng As Range
    Dim minValue As Double

    ' Set the worksheet and input range.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("A1:A10")

    ' Avoid treating an all-blank or text-only range as a real minimum.
    If Application.WorksheetFunction.Count(rng) = 0 Then
        MsgBox "The range contains no numeric values."
        Exit Sub
    End If

    ' Handle calculation errors, including error values in the range.
    On Error GoTo CalculationError
    minValue = Application.WorksheetFunction.Min(rng)
    ws.Range("B1").Value = minValue
    MsgBox "Minimum value: " & minValue
    Exit Sub

CalculationError:
    MsgBox "Could not calculate the minimum. Check the range for errors."
End Sub

To create the macro in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Replace Sheet1 and A1:A10 as needed, then run the procedure from the editor or from Excel’s Macro dialog. On a Mac, Option+F11 opens the editor on many keyboards; you may need Fn as well, depending on your keyboard settings. Excel for the web can store a workbook containing VBA, but it cannot create or run VBA macros.

Understand the range and the result

A Range object tells VBA which cells to examine. The example uses a fixed address, which is straightforward when the data always occupies the same cells. If the input range varies, determine its last row or otherwise identify the correct cells before calling MIN. A macro should not assume that an entire column is the intended dataset if that column also contains headings, notes, or unrelated values.

The output choice is separate from the calculation. The sample writes the result to B1 and also displays it in a message box. Remove the message-box line if the macro should run quietly; change the output address if B1 is not suitable. If you want to keep a history of results, write to a new row each time rather than repeatedly replacing one cell.

Save a workbook containing VBA in a macro-enabled format such as .xlsm. Before running a macro that writes to cells, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, and a macro can overwrite data if its input or output addresses are wrong. Do not enable macros in files from sources you do not trust.

How blanks, text, errors, and zero are handled

When MIN receives a cell reference, it ignores blank cells and text entries in the referenced range; it considers numeric values. This means a number stored as text may be visible in the range but not treated as a numeric candidate. If a result seems unexpectedly high, check whether some apparent numbers are text. In Excel, a value aligned or formatted differently can be a clue, but the underlying cell value is what matters.

Zero is a valid number, not a blank. If the range contains zero and positive numbers, the minimum is zero. A range with no numeric values is a different case: the worksheet MIN function can return zero when there are no numbers to compare. That is why the macro checks COUNT first. COUNT counts numeric cells, so the check also prevents an all-blank or text-only range from being reported as a meaningful zero.

Error values need separate attention. A cell containing an error such as #N/A or #VALUE! can prevent MIN from returning a result. The example uses an error handler so the macro displays a message instead of stopping at an unhandled VBA error. If errors should be excluded, decide how they should be treated and clean or filter the data deliberately; silently skipping them can conceal problems in the source data.

Find the cell that contains the minimum

MIN returns a value, so it does not tell you which row produced that value. To locate the first matching position, use MATCH with MIN. With the values in A2:A100, =MATCH(MIN(A2:A100),A2:A100,0) returns the position within that range: a result of 1 refers to A2, 2 to A3, and so on. The final argument, 0, requests an exact match.

If the minimum occurs more than once, MATCH returns the position of the first match. This can be useful when you want to retrieve a related item, but it is not the same as listing every row tied for the minimum. For a VBA process, you can compare each cell with the calculated minimum and collect all matching rows. Be aware that exact comparisons of calculated decimal values can be sensitive to floating-point precision; for values produced by calculations, a small tolerance may be more appropriate than an exact equality test.

Find a minimum that meets a condition

For a minimum within a category, Microsoft 365 includes the worksheet function MINIFS. For example, with category names in A2:A100 and numeric amounts in B2:B100, =MINIFS(B2:B100,A2:A100,"East") returns the smallest amount whose category is East. MINIFS is available in Microsoft 365 and Excel 2019 or later. A VBA macro can call the worksheet function too, but first make sure the Excel version used to open the workbook supports it.

One subtle point is that MINIFS returns zero when no cells meet its criteria. If zero is also a possible legitimate minimum, that result alone cannot distinguish a match from no matching rows. Check for matching records separately when that distinction matters, and report the no-match case explicitly. For a simple, unconditional minimum, the regular MIN function is easier and more broadly compatible.

Choose VBA or a worksheet formula

Use =MIN(A1:A10) in a cell when you want a visible result that recalculates as the data changes. A formula is usually the simplest choice for a one-off summary, a dashboard, or a report that should update automatically. VBA is more appropriate when finding the minimum is one step in a repeatable task, or when the macro needs to place the result, format it, or use it to control later actions.

There is also a practical difference in sharing. A formula works in the workbook without requiring a macro to run, while VBA requires a desktop version of Excel that supports macros and may be restricted by security settings. If a recipient only needs to see a final result, a formula or a pasted value may be more convenient. If they need the automation, include the macro-enabled workbook and explain what the code changes.

Common problems and checks

The answer is zero: Check for real zeros, then verify that the range contains numeric values rather than only blanks or numbers stored as text. The macro’s COUNT check helps distinguish a genuine zero from no numeric input.
The macro says the sheet cannot be found: Make sure the worksheet tab is actually named Sheet1, or replace that text with the exact tab name. Worksheet names are not case-sensitive, but spelling and spaces must match.
The result is not where expected: Confirm the input range and the output cell. In the sample, B1 is overwritten, while the source range is A1:A10.
The macro stops with an error: Check the range for Excel error values, and verify that the workbook is not protected in a way that prevents the output cell from being changed.
The code does not run: Confirm that macros are permitted for the file and that the workbook is saved in a macro-enabled format. Excel for the web does not run VBA.

Frequently Asked Questions

What is the VBA code to find the lowest number in a range?
Use Application.WorksheetFunction.Min(Range("A1:A10")) to calculate the minimum of that range. In a macro, assign the result to a variable or write it to a destination cell.

Does VBA Min ignore blank cells?
Yes. When MIN is given a range reference, it ignores blank cells and text in the referenced cells, and evaluates numeric values. It does not ignore a numeric zero.

How do I find the minimum in a different worksheet?
Set the range using a worksheet reference, such as ThisWorkbook.Worksheets("Sales").Range("B2:B50"). Replace Sales and the cell addresses with the actual worksheet name and data range.

How can I find the minimum without VBA?
With the numbers in A1:A10, enter =MIN(A1:A10) in a worksheet cell. This is usually simpler when you only need a result that updates with the data.

Can the macro identify the row containing the minimum?
Yes, but MIN alone returns only the value. You can use MATCH with MIN to find the first matching position, or loop through the range in VBA if you need to identify or report every tied row.

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.