Solved: Excel VBA- fastest way to find the row number of a cell with a certain value in a range's column

The following code shows how to do it two different ways:

return the row within the range

return the row number within the worksheet

If the value is not found, the function returns -1.

Sub test()
    MsgBox (getRow(ActiveSheet.Range("B2:C5"), 2, 5))
    MsgBox (getRow(ActiveSheet.Range("B2:C5"), 2, 10))
End Sub

Function getRow(rng As Range, theColumn as Long, theValue As Variant)
Dim r As Long
  
    getRow = -1
    For r = 1 To rng.Rows.Count
        If rng.Cells(r, theColumn).Value = theValue Then
            ' Choose only one of the following assignments depending on what you want
            ' If you want the row with the range
            getRow = r
            ' if you want the absolute row number in the worksheet
            getRow = r + rng.Cells(1, 1).Row - 1
            Exit Function
        End If
    Next r
End Function

Open in new window

If the range C2:C5 contains the values 1,3,5,7 then the first call in the test subroutine will return 4 (the absolute row) and the second will return -1.