Excel VBA: Find the First Row Above a Value Threshold

To find the first row where a cell’s value is greater than a threshold, loop through the relevant range in VBA and test each cell with >. If you mean a row positioned below a known row number, start the search range at the next row; the example below shows both ideas.

What “find a row higher than” can mean

The phrase can describe two different searches. You might want the row containing a number higher than a limit—for example, the first sales amount above 500—or you might want to search only rows with row numbers greater than a starting row. VBA needs to know which meaning applies, because a row’s position and the value stored in that row are separate things.

The example in this guide searches column B for the first numeric value strictly greater than a threshold in E1. It scans from top to bottom, so the first match is the one with the smallest row number in the selected range. To search only below row 10, make the range start at row 11. The comparison is strict: a value equal to the threshold does not qualify. Use >= if equality should count too.

A VBA macro that returns the first matching row

This example assumes the worksheet is named Data, the threshold is in E1, the values to check are in B2:B1000, and the result should appear in F1. Change those references to fit your workbook. The macro ignores blank cells, text that is not numeric, and cells containing errors.

Sub FindFirstValueAboveThreshold()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range
    Dim threshold As Double
    Dim foundRow As Long

    ' Use the worksheet containing this macro.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Check that the threshold is a usable number.
    If IsError(ws.Range("E1").Value2) Then
        MsgBox "The threshold cell contains an error."
        Exit Sub
    End If
    If Not IsNumeric(ws.Range("E1").Value2) Then
        MsgBox "Enter a number in E1."
        Exit Sub
    End If
    threshold = CDbl(ws.Range("E1").Value2)

    ' Change this range to the cells you want to search.
    Set searchRange = ws.Range("B2:B1000")

    ' Scan from top to bottom and stop at the first match.
    For Each cell In searchRange.Cells
        If Not IsError(cell.Value2) Then
            If Len(cell.Value2) > 0 Then
                If IsNumeric(cell.Value2) Then
                    If CDbl(cell.Value2) > threshold Then
                        foundRow = cell.Row
                        Exit For
                    End If
                End If
            End If
        End If
    Next cell

    ' Report the worksheet row number or a not-found message.
    If foundRow = 0 Then
        ws.Range("F1").Value = "Not found"
        MsgBox "No value is greater than the threshold."
    Else
        ws.Range("F1").Value = foundRow
        MsgBox "The first matching row is " & foundRow & "."
    End If
End Sub

The result is the worksheet row number, not the position of the match within the selected range. For example, if the first qualifying value is in B7, the macro puts 7 in F1. If the range contains no qualifying values, F1 displays Not found.

How to change the cells and threshold

Update the worksheet name, threshold address, search range, and output address together so they match your layout. The VBA range B2:B1000 includes both endpoints. If your data starts in row 5, change it to B5:B1000; if it is in column D, use a range such as D2:D1000. The macro searches one column because its purpose is to compare one value per row.

For example, suppose B2:B6 contains 120, 480, 500, 720, and 310, and E1 contains 500. The loop checks each value in order, skips 120 and 480, and does not accept 500 because it is not greater than 500. It finds 720 in B5 and returns row 5. If the condition changes to greater than or equal to 500, the first match is row 4, where the value equals 500.

To use a fixed threshold rather than a cell, assign a number directly in the code, such as threshold = 500, and remove the checks that read E1. A cell reference is often more convenient because you can change the limit on the worksheet without editing the macro.

Search only rows below a particular row

If “higher than” means a greater row number, set the first row of the search range to one below the row you want to skip. For example, to search only after row 10, use ws.Range("B11:B1000"). This searches rows 11 through 1000 and still tests whether each cell’s value exceeds the threshold.

To make the starting row adjustable, define a variable such as startRow As Long, set it to 11, and build the range with ws.Range("B" & startRow & ":B1000"). When constructing ranges dynamically, ensure the start row is not greater than the last row. Otherwise, you may create an invalid range or search a range different from the one intended.

A common mix-up is treating “higher value” as “lower row number.” Excel displays row 20 below row 10, but row 20 has the higher row number. The loop order is controlled by the range order; it does not sort the values. If you want the largest qualifying value rather than the first qualifying row, you need to keep checking matches and track the largest value instead of exiting at the first one.

Find the first match or collect every match

The Exit For statement stops the loop as soon as it finds a qualifying value. That is appropriate when you want the first match in worksheet order. Remove that statement if you want to inspect all qualifying cells, but also change what the macro does with each match. For example, it could write each matching row number to a results column or apply a format to the matching cells.

Be deliberate about where results go. Writing row numbers into a column that contains source data can overwrite information. A safe approach is to choose a clearly empty output column, clear only the previous results you intend to replace, and test the macro on a copy of the workbook first. If there are many matches, a results list is more informative than a single message box.

Blanks, text, errors, and numeric-looking values

The sample checks for errors before examining a cell, then ignores empty cells and values that VBA does not recognize as numeric. A cell containing a formula that returns an empty string is also skipped. Text such as “pending” is not compared to a numeric threshold, preventing a type mismatch in the comparison.

Numeric-looking text, such as the text string “720”, may be accepted by IsNumeric and converted by CDbl. If the column should contain only genuine numbers, consider cleaning or validating the data rather than silently relying on text conversion. Number formats do not determine the underlying value: a cell formatted as currency still contains a number that can be compared.

Dates are stored by Excel as serial numbers. As a result, a date in the search column can be treated as a number and compared with a numeric threshold. If the data mixes dates and ordinary quantities, add a rule to distinguish them or use separate columns; otherwise a date serial could unexpectedly qualify. Also remember that IsNumeric and CDbl interpret text according to regional settings, so imported decimal or thousands separators deserve attention.

Why a loop is often clearer than Range.Find

Excel’s VBA Range.Find method is useful for locating text or an exact value, but it does not offer a simple “find the next cell whose numeric value is greater than this threshold” condition. Find settings such as search direction and what to look in can also persist from earlier searches, so a Find-based macro should specify its arguments carefully.

For a straightforward numeric comparison, a loop makes the rule explicit and easy to adapt. It also lets you decide how to handle blanks, errors, and text. For very large datasets, looping through each cell can be slower than reading a range into a VBA array and checking the array in memory. For a few thousand cells, the direct loop is usually easier to understand; optimize only if performance becomes a real issue.

If you only need to identify or display qualifying records, Excel’s built-in filter tools may be enough and avoid VBA entirely. A macro is more useful when the search is repeated, must follow custom rules, or needs to return a row number for another procedure.

Run the macro safely in Excel

On Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the code into the module, and change the worksheet and cell references. Return to Excel and run the macro from the Macro dialog, commonly opened with Alt+F8. On Mac, Option+F11 commonly opens the Visual Basic Editor; depending on the keyboard, you may need Fn as well. Excel for the web can open a workbook but cannot create or run VBA macros, so use the desktop app.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, even if the change looks small. Save a workbook that contains VBA as an Excel Macro-Enabled Workbook (*.xlsm), and run macros only from files you trust. If the Developer tab is not visible, enable it through Excel Options on Windows or Excel settings on Mac; the exact settings layout can vary by platform and release.

The code uses ThisWorkbook, meaning the workbook that contains the macro. This is safer than relying on whichever workbook happens to be active, but it means the Data worksheet must exist in that workbook. If you put the macro in a separate personal macro workbook, change the workbook reference intentionally rather than assuming the active sheet is the right one.

Frequently Asked Questions

How do I find the first row with a value greater than a number in VBA?
Loop through the relevant range from top to bottom, test each numeric cell with >, save cell.Row when the condition is true, and stop if you only need the first match.

How do I search rows after row 10?
Start the range at row 11. For example, a search in column B can use ws.Range("B11:B1000"). This excludes row 10 and every row above it.

Does greater than include a value equal to the threshold?
No. The operator > means strictly greater than. Use >= when values equal to the threshold should count.

How can I find the last row that is greater than the threshold?
Do not exit the loop at the first match. Keep assigning the matching cell’s row to a variable as the loop continues; after the loop, that variable will hold the last qualifying row in the range’s scan order.

Why does my macro say a row was not found?
Check that the worksheet name and search range are correct, that the threshold is numeric, and that at least one cell is strictly greater than it. Also check whether values are stored as text, whether the search starts below the matching row, and whether the code is running against the workbook you intended.

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.