Get the last row none empty value in specific Column

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.

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)"

1 Like

@paul.placintar Thanks for the reply.

i got below error. Any idea why?

@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

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

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

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

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.