• 464K Members
• 9,124 Online
• 561K 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)`

# 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!
Highlighted

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

@PerMorris , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 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
29 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies