Get the last row none empty value in specific Column

[MDias]

Hi All,

Please help to get the last row none empty value in specific Column(PO Number) -Sheet1 and compare with last none empty row in specific Column(PO Number) -sheet2 if both are same it should prompt validation success.

Thanks for your support.

[paul.placintar]

You’ll read each sheet in a dataTable -> sheet1Table and sheet2Table

Assuming you’ll have strings, assign to 2 variables the last value like this

dim var1 as string = sheet1Tabled.AsEnumerable.Where(function(x) not String.IsNullOrEmpty(x.Field(of string)(“PO Number”).ToString)).Last().Item(“PO Number”).ToString

The same for the last value from the second sheet

dim var2 as string = sheet2Tabled.AsEnumerable.Where(function(x) not String.IsNullOrEmpty(x.Field(of string)(“PO Number”).ToString)).Last().Item(“PO Number”).ToString

Then you’ll just have to compare them

If you’ll get an error, change maybe from string to integer or double at "field(of string)"

[MDias]

sheet1Table

@paul.placintar Thanks for the reply.

i got below error. Any idea why?

[paul.placintar]

@MDias
Add a Console WriteLine before the Assign with sheet1Table.rows.count.tostring to check if the table is ok

Else, i’m gonna need your input to test the code I wrote is working correctly

[MDias]

@paul.placintar
Please find attachedLastRowCompare.zip (1.8 MB)

[paul.placintar]

LastRowCompare.7z (1.4 MB)

This should be working.

What I suggest you is to use workbook read and write range

And maybe make a function that will not read the whole 9000 lines to make the process faster

[MDias]

@paul.placintar Thanks a lot for your support It works now