Get last index of a column that contains value

Hi Everyone,
I need to copy data from a workbook(A) to another(B), now I’m using read range and write range between two workbooks. The problem happened at read range, I need to get the specific column’s lat row index that contain value form A , so that I can know which range to read, but sometimes the column will have empty cell in between.(like the picture below)
For example I need to get “12” in this picture.

Hi @tina.jl.liu ,

while doing write range remove empty row in the existing table.

DataTable name->DataTableName
Use assign activity
DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

Regards,
Arivu

1 Like

Hi,

Could you pls refer the below documentation from uipath which help you to find the last index of the column. try and let us know. thanks.

@tina.jl.liu

Welcome to forums

From the datatable you can write as below

StringVariable = DatableVariable.Rows.Count.ToString

You can use StringVariable, To Match with Excel with header you can add as below
StringVariable = (DatableVariable.Rows.Count+2).ToString

because Index starts with 0, but excel starts with 1

Hope this will help you

Thanks

StringVariable = DatableVariable.Rows.Count.ToString
I’ve tried this one it will get 1048576…

I found the activity, but what to input for InArgument data type.
Can you give me an example. Thank you!

Hi,

It might be filled by whitespace to the end of row. So can you try the following steps?

1.Read whole data as datatable using ReadRange activity with header. (Let’s say dt)

2.the following will return last index where data exists.

lastIndex = dt.AsEnumerable.Select(Function(r,i) if(String.IsNullOrWhiteSpace(r(0).ToString),-1,i)).Max+2

Regards,

May I ask “+2” is because of the two blank row?

Hi,

May I ask “+2” is because of the two blank row?

No. If we read table using ReadRange activity with header, Excel row#1 is assigned as header, Excel row#2 is assigend as datatable row#0…

So as it’s 0-based index and header exists, we need to add 2.
(Or it might be +3, if you need to get row number of starting blank area)

Regards,

Thanks!! I got it.

1 Like

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