How to get last row cell value in particular column

Help Activities
, ,
1

Hi Team,

I want to copy particular range in excel value and paste to another excel ,starting cell value static(A2) but last cell value dynamic

Status column last cell value is dynamic (B6 or something will come)

Please help me anyone for this

Input excel:
image

expected output:
A2:B6 copy to paste another sheet

2

HI @Raja.G

  • Read Range first and store them in the datatable
  • then in the copy paste range You can use this as the range
"A1:"+UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(dt.Columns.Count)+(Dt.Rows.Count+1).ToString

Regards
Sudharsan

1 Like
3

Hi @Raja.G, one way to do it is to read data using ‘Read Range’ activity and not having ‘Has Headers’ selected, post that use ‘Remove Data Row’ activity on that DataTable and pass ‘RowIndex’ as 0, which removes the first row which is actually the headers. This way you read entire data dynamically and get only actual data without actual headers.

4

@Raja.G,

Based on your query, I understood in two ways:

  1. To copy the whole table to another sheet, we can give this empty string “” to Range Property. It will self-identify till which cell (row or column) or the cell where the table ends.
    image

  2. If the last cell value is dynamic. Then let’s store this dynamic to a string variable, say LastCellNumber = “B6”.
    Then we can pass this “A2:” + LastCellNumber to the range property.

Hope this helps,
Warm Regards,
Ashwini K

5

Hi @Sudharsan_Ka ,

Copypaste range coming like below,

Where i will update

image
image727×381 11.7 KB

6

This is the modern excel you see the classic ones by enabling the Show classic in filters @Raja.G

image

Regards
Sudharsan

8

Hi @Sudharsan_Ka ,

Working fine thanks, but I want particular column last cell value only “Status” another column also coming to sheet 1

Please help me for this

image

9

You want only name and status in the pasted range @Raja.G ?

If yes try this range

"A1:"+UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(dt.Columns.Count-1)+(Dt.Rows.Count+1).ToString

Or

you have read whole datatable right

with that dt use filter datatable and keep only the column you wanted and then paste the range

image
image1131×449 28.1 KB

Note : use this filter datatable before copy paste range

Regards
Sudharsan

10

If you want only the count of particular column of status you can use this expression for getting the row count

(dt.DefaultView.ToTable(False,"Status").Rows.count+1).Tostring

"A1:"+UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(dt.Columns.Count)+(dt.DefaultView.ToTable(False,"Status").Rows.count+1).Tostring.ToString

Regards
Sudharsan

12

Hi @Sudharsan_Ka ,

Working fine thanks

13

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