Forum Discussion

PerMorris's avatar
PerMorris
Copper Contributor
Jul 08, 2019

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

excel
Formulas and Functions

5 Replies

  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    Jul 08, 2019

    PerMorris 

    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

    https://vba-tanker.com/

Resources