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
Big thanks Bernd, worked brilliantly!
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies