Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-742826%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20certain%20table%20within%20data%20sheet%20and%20the%20find%20last%20row%20in%20that%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742826%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372723%22%20target%3D%22_blank%22%3E%40PerMorris%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Ei%20write%20an%20userdefined%20function%20to%20do%20the%20job.%20See%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EFunction%20GetLast(strKEy%20As%20String)%20As%20Double%3CBR%20%2F%3EDim%20rngFind%20As%20Range%3CBR%20%2F%3EDim%20lngLastRow%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20rngFind%20%3D%20ActiveSheet.Range(%22B%3AB%22).Find(what%3A%3DstrKEy%2C%20lookat%3A%3DxlWhole)%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20rngFind%20Is%20Nothing%20Then%3CBR%20%2F%3ElngLastRow%20%3D%20Cells(rngFind.Row%2C%20%22B%22).End(xlDown).Row%3CBR%20%2F%3EGetLast%20%3D%20Cells(lngLastRow%2C%20%22E%22).Value%3CBR%20%2F%3E%3CBR%20%2F%3EElse%3CBR%20%2F%3EGetLast%20%3D%200%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Function%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22The%20vba-Tanker%22%20href%3D%22https%3A%2F%2Fvba-tanker.com%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EThe%20vba-Tanker%20-%20a%20database%20full%20of%20usefull%20macros%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743305%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20certain%20table%20within%20data%20sheet%20and%20the%20find%20last%20row%20in%20that%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743305%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372723%22%20target%3D%22_blank%22%3E%40PerMorris%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20INDEX%2FMATCH%20that%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20416px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122600iCCEDE07405ADDE82%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20H5%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24E%3A%24E%2CMATCH(1%2CINDEX(--ISBLANK(INDEX(%24B%3A%24B%2CMATCH(%24G5%2C%24B%3A%24B%2C0))%3A%24B1000000)%2C0)%2C0)%2BMATCH(%24G5%2C%24B%3A%24B%2C0)-2)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744212%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20certain%20table%20within%20data%20sheet%20and%20the%20find%20last%20row%20in%20that%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744212%22%20slang%3D%22en-US%22%3EAbsolutely%20brilliant%2C%20you%20saved%20my%20day!%20Thanks%20a%20lot.%3CBR%20%2F%3E%3CBR%20%2F%3E%2FPer%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744213%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20certain%20table%20within%20data%20sheet%20and%20the%20find%20last%20row%20in%20that%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744213%22%20slang%3D%22en-US%22%3EBig%20thanks%20Bernd%2C%20worked%20brilliantly!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-742562%22%20slang%3D%22en-US%22%3ESOLVED!%20Find%20certain%20table%20within%20data%20sheet%20and%20the%20find%20last%20row%20in%20that%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742562%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20problem%20that%20I%20can't%20find%20the%20solution%20to.%20I%20have%20(as%20you%20can%20see%20in%20the%20picture%20bellow%20a%20data%20sheet%20with%20different%20tables.%20Each%20table%20is%20divided%20by%202%20empty%20rows%20as%20you%20can%20see.%20There%20are%20a%20lot%20of%20tables%2050.000-100.000%20rows.%20So%20I%20can%20ofcause%20use%20index%20match%20to%20find%20the%20correct%20table%2C%20using%20the%20ID%2C%20no%20problemo%20so%20far.%20Now%20my%20problem%20is%20that%20I%20need%20to%20find%20the%20last%20row%20in%20that%20table%20and%20there%20are%20different%20number%20of%20rows%20in%20the%20tables%2C%20as%20you%20can%20see%20in%20the%20picture.%20So%20I%20need%20to%20get%20the%20salary%20for%20that%20ID%20which%20would%20have%20been%20easy%20if%20it%20was%20the%20same%20amount%20of%20rows%20in%20all%20tables%20but%20it's%20not%3F%20%2FPer%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122552iF2BF695713E8B24A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20problem.JPG%22%20title%3D%22Excel%20problem.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-742562%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744282%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20certain%20table%20within%20data%20sheet%20and%20the%20find%20last%20row%20in%20that%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744282%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372723%22%20target%3D%22_blank%22%3E%40PerMorris%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
PerMorris
New Contributor

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

Excel problem.JPG

 

I have a

5 Replies

@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

The vba-Tanker - a database full of usefull macros

@PerMorris 

With INDEX/MATCH that could be

image.png

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)
Absolutely brilliant, you saved my day! Thanks a lot.

/Per
Highlighted
Big thanks Bernd, worked brilliantly!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
30 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
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies