CODEWITHSUNDEEP
excelvba
Danny
Danny

Reputation: 121

Find cell farthest to the bottom of spreadsheet containing specific value

I have a spreadsheet with multiple tables containing the employee names. I'm trying to locate the cell farthest to the bottom of my spreadsheet containing the value "Person".

Sub findperson()

Dim a As Range
    
    With ThisWorkbook.Worksheets("sheet1").Columns(1)
    
        Set a = .Find("Person", LookIn:=xlValues)
        MsgBox a.Row
    
    End With

End Sub

My code locates the first value cell which contains "Person" e.g. returns 2 for a.Row when I would like it to return 12 (the bottom-most value).

enter image description here

Upvotes: 0

Views: 43

Answers (1)

Teamothy
Teamothy

Reputation: 2016

You need to set .searchdirection of .Find to xlPrevious

Set a = .Find("Person", LookIn:=xlValues, searchdirection:=xlPrevious)

Upvotes: 3

Related Questions