Finding value and returning row number
 

Hi,

I was wondering what command(s) would be best for finding a value's row
and returning it as an integer?

Thanks!
Merritts


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=564512

Finding value and returning row number
 

Hello Merritts,

There are a few ways to do this...

Code:
--------------------

1) Using the Active Cell
R = ActiveCell.Row

2) Using a Range to find the First Row
R = Range("A2") 'R = 2
R = Range("A34:Z1250") 'R = 34, the first row in the range

3) Using a Range to find the Last Row
With Worksheets("Sheet1") 'R = last non blank cell in column A on Worksheet1
R = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=564512

Finding value and returning row number
 

You could also use the Find method

For example, to search Sheet1 for the word "Test":

Sub test()
Const strCriteria = "Test"
Dim rngCell As Range

With Worksheets("Sheet1")
Set rngCell = .Cells.Find(What:=strCriteria, _
After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With

If Not rngCell Is Nothing Then _
MsgBox rngCell.Row

End Sub

Check VBA help for more information as you may need to change some of the
search parameters.

"merritts" wrote:


Hi,

I was wondering what command(s) would be best for finding a value's row
and returning it as an integer?

Thanks!
Merritts


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=564512