• 505K Members
• 4,699 Online
• 602K 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 Double
Dim rngFind As Range
Dim lngLastRow As Long

Set rngFind = ActiveSheet.Range("B:B").Find(what:=strKEy, lookat:=xlWhole)

If Not rngFind Is Nothing Then
lngLastRow = Cells(rngFind.Row, "B").End(xlDown).Row
GetLast = Cells(lngLastRow, "E").Value

Else
GetLast = 0
End If

End Function

Best regards

Bernd

The vba-Tanker - a database full of usefull macros

Highlighted

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

# 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
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Discussion - Updating our interface with Fluent touches
Elliot Kirk in Discussions on
102 Replies