How to get last used cell +1 number from specific column

Help Studio
1

Hello,

I am trying to update the data into one column and for that I need to get the last used cell reference +1 number.
Ex. My A Column have data till A14 and my Q column have data till Q9. So in this case my task is to start updating the data from Q10.
image

I have used Read range activity but which give me entire table count that is 14 and I want on Q column count.

I have tried with below activity also which is working fine if there is data available after Header but gives error if there is no data after Header.
image

1 Like
2

You can try Append Range activity and see if it’s what you’re looking for.

3

Hope this expression would help you resolve this

This gives the last row and column cell value

dt.Rows((From row in dt.AsEnumerable() Select Convert.Tostring(row(“last_ColumnName”))) .ToList().Count-1)(“last_columnname”)

To have the row index alone have this expression alone
(From row in dt.AsEnumerable() Select Convert.Tostring(row(“last_ColumnName”))) .ToList().Count-1

Cheers @nilesh.mahajan

4

Hi @nilesh.mahajan

Use For Each Row in Data Table Activity
If Condition
CurrentRow(“ABCD”).ToString<>“”

Refer to the screenshot

image
image950Ă—646 17.8 KB

Regards
Gokul

7

You can use a read range activity with “Q” as the range in to a datatable
image
datatable.rows.count+1 will give you that cell index

8

readin the excel with read range - dtData

Assign activity
LHS: idxLast | Int32
RHS:

dtData.AsEnumerable.ToList.FindLastIndex(Function (x) Not (isNothing(x(colNameOrIndex)) OrElse String.IsNullOrEmpty(x(colNameOrIndex).toString.trim)))

later add an offset for the excel first row (headers when used) + 1 as returne index is zero based