• 414K Members
• 6,542 Online
• 479K Conversations

New Contributor

# SOLVED! Find certain table within data sheet and the find last row in that table

Hi, I have a problem that I can't find the solution to. I have (as you can see in the picture bellow a data sheet with different tables. Each table is divided by 2 empty rows as you can see. There are a lot of tables 50.000-100.000 rows. So I can ofcause use index match to find the correct table, using the ID, no problemo so far. Now my problem is that I need to find the last row in that table and there are different number of rows in the tables, as you can see in the picture. So I need to get the salary for that ID which would have been easy if it was the same amount of rows in all tables but it's not? /Per

I have a

5 Replies

# Re: Find certain table within data sheet and the find last row in that table

Hi,

i write an userdefined function to do the job. See attached file.

`Function GetLast(strKEy As String) As DoubleDim rngFind As RangeDim lngLastRow As LongSet rngFind = ActiveSheet.Range("B:B").Find(what:=strKEy, lookat:=xlWhole)If Not rngFind Is Nothing ThenlngLastRow = Cells(rngFind.Row, "B").End(xlDown).RowGetLast = Cells(lngLastRow, "E").ValueElseGetLast = 0End IfEnd Function`

Best regards

Bernd

The vba-Tanker - a database full of usefull macros

# Re: Find certain table within data sheet and the find last row in that table

With INDEX/MATCH that could be

in H5

`=INDEX(\$E:\$E,MATCH(1,INDEX(--ISBLANK(INDEX(\$B:\$B,MATCH(\$G5,\$B:\$B,0)):\$B1000000),0),0)+MATCH(\$G5,\$B:\$B,0)-2)`
Highlighted

# Re: Find certain table within data sheet and the find last row in that table

Absolutely brilliant, you saved my day! Thanks a lot.

/Per

# Re: Find certain table within data sheet and the find last row in that table

Big thanks Bernd, worked brilliantly!

# Re: Find certain table within data sheet and the find last row in that table

@PerMorris , you are welcome

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies