How to get the last column header and the data of last row value from the excel sheet

I have to get the last column header and last row count and use delete range to delete the values in Excel.

I have got the last row value count using the below.

When i tried to get the column header (A, B, C, D…I), it returns the count as “9” instead of “I”
I used this “valueDT.Columns.Count”

Please explain me how to get column header and use delete range to delete the cell value.
And also would like to know how to delete the entire sheet data?


Let’s go one by one

  1. For this


Will give the column name

  1. For this

To get the row count


Count usually starts from 1

If you want to know the last row index that is index position
Then it is -1 of count
Reason is Index start value is 0

So it’s like this

ValueDT.Rows.Count-1 Will give the last row index position

  1. And finallye to delete the data from sheet we have a look on this thread
    It has a solution

Cheers @Subha_Sundara_moorthi

1 Like


Can you try to use UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter method?

For example, UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(9) returns “I” as the following.


So, we can get address of right-bottom corner as the following expression.


(Assumed there in no header in table. If there is table header and you read it with AddHeader option, row number need to be added 1)

If you want to delete all cell of table and table starts from “A1”, the range string is


Hope this helps you.


1 Like

I need the Column index as "A/B/C/D/… " or say whatever.

The above returns the result as Column8.

I have the data in excel say 27 rows (including headers). To get the last row count, i will use valueDT.Rows.Count
I’m not clear with the -1 of count (last row index position). When i use this ValueDT.Rows.Count-1
It returns 26

This is total no of rows in a datatable
That’s correct

I mentioned that in addition for a knowledge that in order to get the last row index we need to include -1 as
index starts from 0 for first row
Count starts from 1 for first row

And for this


Even here if you see we are passing column index and that’s why we are including -1

Hope this clarifies
Cheers @Subha_Sundara_moorthi

1 Like

Thank you both :slight_smile:

1 Like


How can i achieve to print the last row of last column value in output panel.

I tried this,

where Excel_Datatable is the datatable name and Get_Column returns right bottom cornor value UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(Excel_Datatable.Columns.Count)+Excel_Datatable.Rows.Count.ToString


Can you try the following?



1 Like


We can try like this



Excel_Datatable = datatable name

Excel_Datatable = getting the value with Rows

For Rows method

Rows(row index)(column index) = row index and column index both starts from 0
That’s why

Excel_Datatable.Rows.Count-1 = gives the last row index

Excel_Database.Columns.Count-1 = gives the last column index

Hope this clarifies the query

Cheers @Subha_Sundara_moorthi

1 Like

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