VBA Find the Maximum Value in an Excel Column: How-To

To find the largest numeric value in an Excel column with VBA, use the worksheet function Max on the range you want to check. The macro below finds the last used row in column B, checks for numbers, and displays the maximum in a message box.

How the maximum calculation works

Excel’s MAX function returns the greatest numeric value in a range. VBA can call the same calculation through Application.WorksheetFunction.Max. You supply the range, then store or display the returned value. This is usually simpler and less error-prone than comparing every cell yourself.

For a worksheet formula, assume the values to check are in B2:B6, containing 12, 8, 25, a blank cell, and 17. The formula is:

=MAX(B2:B6)

It returns 25, the largest number in that range. In VBA, the equivalent calculation is performed on a Range object rather than by entering a formula in a worksheet cell. The macro later in this article uses that approach and avoids checking the header in B1.

The result is the value, not necessarily the address of the cell containing it. If two cells share the maximum, the maximum value is still returned just once. If you also need the row or cell address, you need an additional lookup step.

A VBA macro for a changing column range

This example uses column B on a worksheet named Data. It finds the last non-empty cell in B, builds a range from B2 to that row, and reports the maximum. Change the worksheet name or column letter to match your workbook.

Sub ShowMaximumInColumn()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim lastRow As Long
    Dim maxValue As Double

    ' Use the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last non-empty cell in column B.
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Stop if column B has no data below the header.
    If lastRow < 2 Then
        MsgBox "No data found in column B below the header."
        Exit Sub
    End If

    Set dataRange = ws.Range("B2:B" & lastRow)

    ' Avoid treating an empty or text-only range as a maximum of zero.
    If Application.Count(dataRange) = 0 Then
        MsgBox "No numeric values found in column B."
        Exit Sub
    End If

    ' Calculate and display the largest number.
    maxValue = Application.WorksheetFunction.Max(dataRange)
    MsgBox "Maximum value: " & maxValue
End Sub

lastRow is a row number, so it is declared as a Long. The range begins at row 2 to exclude a typical header. Application.Count counts numeric cells; if it finds none, the macro exits rather than reporting zero as though zero were the answer. This matters when the range is blank or contains only labels.

The macro assumes the worksheet is named exactly Data and that the values are in column B. If either differs, update those references. If your data has no header and starts in B1, change the starting row in the range and adjust the empty-range check accordingly.

Choose a fixed or dynamic range

A fixed range is suitable when the data size is known and stable. For example, a macro that always checks B2:B1000 can use that range directly. A dynamic last-row method is more convenient when rows are regularly added, but it depends on how the column is laid out.

Use a fixed range when the input area has a defined size, or when values below the intended data should never be included.
Use a last-row range when new records are appended to the same column and the last used cell marks the end of the data.
Use an Excel Table column when the data is already in a Table and you want the range to expand with new table rows. The precise structured reference depends on the table and column names.

The macro’s last-row search looks for the last non-empty cell in column B, not the last numeric cell. A note, formula, or other content far below the records can therefore expand the range. Text is ignored by MAX when it is part of a referenced range, but that extra content may still make the macro examine more cells. Keep the data column organized, or choose a fixed boundary if unrelated entries appear below it.

A formula that displays an empty string is still a formula in its cell, so it can affect where Excel finds the last used row. In that case, the resulting range may be larger than the visible data. That is usually harmless for the calculation, but it is worth checking if the macro appears to include an unexpected section of the sheet.

What counts as a maximum

For a range reference, MAX considers numeric values and ignores blank cells and text. This makes it useful when a column includes labels or occasional empty cells. It also means a text value such as “unknown” is not treated as a number. Numeric-looking text may not behave like a true numeric cell, so convert imported data to numbers if it should participate in the comparison.

Negative numbers are handled normally. For example, if the range contains -14, -6, and -21, the maximum is -6, because it is the greatest value even though it is below zero. Do not replace the maximum with zero just because zero seems like a convenient starting value; doing so would give the wrong result when every entry is negative.

Excel stores dates as serial numbers. MAX therefore returns the latest date when all relevant values are dates, but a VBA message box may show the underlying serial number rather than a date-formatted display. If the result must be shown as a date, write it to a cell formatted as a date or format the displayed value appropriately. A column mixing dates and ordinary numbers is compared by the underlying numeric values, which may not match the intended business meaning.

Cells containing Excel errors deserve attention. A range with an error can cause the worksheet-function call to fail rather than return a usable maximum. Resolve or deliberately handle errors before running the macro; do not assume MAX will silently skip every error. Also remember that the number format affects how a result looks, not which value is largest.

Display or reuse the result

The sample sends the result to a message box, which is convenient for a quick check and does not modify the worksheet. For a repeatable report, you may prefer to store the result in a designated output cell, such as D2, or use it in another calculation. Choose a cell that will not overwrite existing data, and consider adding a label so the result is clear to other workbook users.

If the maximum can change after each data refresh, a worksheet formula may be more appropriate than a macro because it recalculates automatically. With values in B2:B1000, enter =MAX(B2:B1000) in an output cell. That formula returns the current maximum without requiring the user to run code. A macro is more useful when the result is part of a larger automated task, such as producing a report or checking several worksheets.

To identify the cell containing the maximum, decide how ties should be handled. A lookup can return the first matching occurrence, while a separate search or loop can collect every matching row. The maximum calculation alone cannot tell you whether there are several cells with the same value, so do not interpret the message box as identifying a unique record.

Run and save the macro safely

On Windows in Excel for Microsoft 365, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the macro into the module, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). To run it from Excel, use Alt+F8, select ShowMaximumInColumn, and choose Run. If the Developer tab is visible, Developer > Macros also opens the macro list.

On a Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut is often Option+F11, sometimes with Fn depending on the keyboard settings. Excel for the web can open and edit many workbooks, but it does not run VBA macros. Use the desktop Excel app to run this code.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, even though this particular example only displays a message. In a larger macro, an incorrect range or output destination could alter workbook data. Run code only when you understand what it references, and enable macros only for files you trust.

Common problems and fixes

“Subscript out of range”: Excel cannot find a worksheet named Data in the workbook referenced by ThisWorkbook. Check the spelling, including spaces, or update the worksheet name in the code.
The result is zero or no numeric values are found: Check that the intended cells contain real numbers, not numbers stored as text, and confirm that the macro starts at the correct row.
The macro reports a larger-than-expected value: Inspect the full range, including hidden rows and cells below the visible records. MAX does not ignore hidden numeric cells.
The macro stops with an error: Look for error values such as #N/A or #VALUE! in the range. Clean or handle those errors before calling MAX.
The output appears as a date serial or an unexpected format: The underlying result may be correct, while its display format is not. Apply the desired number format where the result is shown.

Also check that the macro is stored in the intended workbook and that macros are permitted by your organization’s security settings. If the sheet is protected or the workbook structure is restricted, other operations in a larger macro may require permissions, although this example only reads the range and displays a message.

When to use VBA instead of MAX in a cell

Use the worksheet formula when you want a visible, continuously updated answer and do not need automation. Use VBA when finding the maximum is one step in a repeatable workflow, when you need to check multiple ranges, or when the result should trigger another action. In either case, the calculation is still a maximum over numeric values; VBA does not make unsuitable or inconsistent source data meaningful.

For one column and one answer, the formula is usually the shortest solution. For a macro-driven workbook, the VBA version gives you control over which worksheet and range are checked and what happens afterward. If only visible rows should count, ordinary MAX is not enough because it includes hidden values; choose a method designed for the filtering or visibility rules you need rather than assuming the standard maximum follows the screen display.

Frequently Asked Questions

How do I find the maximum value in column A with VBA?
Set the worksheet range to column A instead of column B, and use the appropriate starting row. For example, if row 1 is a header, check from A2 through the last used row in A.

Does VBA MAX ignore blanks and text?
When MAX receives a cell range, it ignores blank cells and text entries while comparing numeric values. Numeric-looking text may not be treated as a number, and error values can cause the calculation to fail.

How can I get the maximum from a column with negative numbers?
Use the same MAX method. It correctly returns the greatest value, such as -6 from -14, -6, and -21. Check that the range contains numbers and do not initialize a comparison to zero if all values might be negative.

Can I find the cell address as well as the maximum?
Yes, but the MAX calculation returns only the value. Add a lookup or search for that value in the range to get a matching cell; if maximum values can tie, decide whether you need the first match or every matching cell.

Can I run VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the code.

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.