Find Last Cell Number in each Excel Column

Learning & Development Programs Reboot Your Skills 2021
1

Capture
Capture935×571 14.9 KB

How Can I find LAST CELL NUMBER WITH VALUE IN EACH excel column, like (see attachmen)

Col A- 1000
Col B - 1003
Col C - 1000
Coll H - 1010

2

Hi @pasingh,

You can check below links

Thanks,
Neelima.

1 Like
3

Hi
I am using - DatatableVariable.Rows.COUNT.TOSTRING but it not giving me last cell number for each cell instead the Last cell number in the excel sheet as whole.

4

Hi @pasingh,

With reference from the link @1996 shared, you can use the below LINQ to find the last cell number of any column.

(dt.Rows.IndexOf((From row in dt.AsEnumerable().Where(Function(x) Convert.ToString(x(“ColumnName”)).Trim<>“” ) select row).ToList.Last)+2).ToString

The index starts from 0 and there is an header, so we add 2 at the end.

Happy coding! :slight_smile:

1 Like
5

Hi Udhay

Could you please send me a sample .xaml file if available.
Thanks

6

Hi @pasingh,

I could not upload xaml files, but here is the screenshot. I have expanded the assign activity.

image
image1279×524 20.2 KB

8

@pasingh
find some starter help here:
GetLastValueIndex_AllCols.xaml (10.8 KB)

From data:
grafik

it will calculate:
grafik

ensure following package is referenced:
grafik

9

Thanks very much.