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
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 SubFunction 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 rEnd Function
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.
If the value is not found, the function returns -1.
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.