Finding last used row in a name range and to ignore data below the named ranged

[Shananarocks]

Hi, I am quite a newbie to MS Excel VBA although I can code quite well in MS Access.
Basically in Excel, I have a named range "NameAge" (A10:B20). I need to find the row that "Joe" resides.
However, most VBA codes will either result in giving me the last row which is row 20 of the named range or row 23 where "Charles" is.
Appreciate any help.
Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Range("NamedAge").Find("*", , , , xlByRows, xlPrevious, False, , False).Row

 

[Shananarocks]

Hi & welcome to MrExcel.
How about

Range("NamedAge").Find("*", , , , xlByRows, xlPrevious, False, , False).Row

Thanks for the prompt reply but I got an error message "Run-time error "1004" , Method 'Range of object'_Worsheet' failed".
Using Cells.find I was only able to find the position of "Charles" at row 23.

Set sht = ActiveSheet
LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Debug.Print "Last found used row: " & LastRow

Dim LastRow_Find As Long
LastRow_Find = sht.Range("NamedAge").Find("*", , , , xlByRows, xlPrevious, False, , False).Row
Debug.Print "LastRow_Find: " & LastRow_Find

 

[Fluff]

Is the named range on the active sheet when you run that code?

 

[Shananarocks]

Is the named range on the active sheet when you run that code?

LOL!!! It should be "NameAge" instead of "NamedAge". My apologies. Thanks for the instant solution. May you have the best new year and lots of good wishes too. !!!!

 

[Fluff]

Glad to help & thanks for the feedback.