Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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

Hi

Given a certain range, what is the fastest way to find the row number of a cell with a certain value in column 2 ?
Microsoft Excel

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
tdlewis

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.
ASKER CERTIFIED SOLUTION
terencino

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Murray Brown

ASKER
Thanks very much
Your help has saved me hundreds of hours of internet surfing.
fblack61