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


Given a certain range, what is the fastest way to find the row number of a cell with a certain value in column 2 ?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
terencinoConnect With a Mentor Commented:
This uses the Find method, in the Worksheet_Change event...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2"), Target) Is Nothing Then
    ActiveSheet.Range("A4").Value = ActiveSheet.Columns("B:B").Find(Range("A2").Value).Row
End If
End Sub

Open in new window

Pretty quick
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.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.