Find last row and coloumn in excel sheet

Help
,
1

How to find last row and coloumn in excel sheet for string and integer both

1 Like
2

Hi @mahesh.shelke,
If you read the entire excel sheet without specifying the range, you would know how much rows and columns it has. I believe you could begin with that strategy.

2 Likes
3

Hi @mahesh.shelke,

Use Read Row, Read Column activity to get the Row, Column counts respectively.

When you use Read Row or Read Column the Output will be stored in a List, then use [You_List_Name].COUNT.TOSTRING to get the Row/Column count.

4 Likes
4

Hi I got it…

If we use Read Row or Read Column it will give the total count of rows and not the last row or coloumn.

I tried it with READ RANGE and DatatableVariable.Rows.COUNT.TOSTRING and it worked.

Thanks

6 Likes
5

Did you put the "DatatableVariable.Rows.COUNT.TOSTRING " into the output of Read Range? When I do I get an error: Value Type ‘String’ can not be converted to “System.data.datatable”. my variable rowsCount is set as a variable type of datatable as well. Any thoughts?

6

Hey Don’t put "DatatableVariable.Rows.COUNT.TOSTRING " directly into an output of read range.
Follow this steps:
Take excel application scope then give your excel path in that.
Inside excel application scope drag Read Range Activity and in output of ReadRange create data table variable to store data from excel.
Then use assign activity and in create variable “countExcel”(take type as int) and inside TO field write "DatatableVariable.Rows.COUNT ".

3 Likes
7

Hi
Your definition is working fine for getting the total count of rows…but it is not fetching the total count of columns in the excel…how to get both the row and column count???

8

Hi Nirsha,

This might help you.

"Row count : "+dtVariable.Rows.Count.ToString
"Column count : "+dtVariable.Columns.Count.ToString

Regards,
Rahamat

9

yea i did that…thank you!!!
and one more thing
i have got the range and column index also…i just want combine it and select the range
for example: starting range is A1 that is fixed, using row count i have got the count as 153 and column header as AS…how to combine this AS+153 in read range

ReadRange=“A1 : AS153” i want this format…help me out of this

10

Hi,

Use below code to get number of columns in excel.
First read Excel in data table and then use following code:

datatableName.Columns.Count.ToString

Thanks,
Pravin Patil
(9890924097)
Innowise

11

“A1:”+“AS:” + datatableName.Rows.Count.ToString

3 Likes
12

Hi
But that AS is also not fixed…a variable is holding that column index.if so how to represent that

13

I have done with this code its working exactly how i want “A1:” + Column_Name.ToString + countRows.ToString.
Thank you @pravin.patil

14

Welcome…And All the best

15

Hi
But while getting the count it is not taking the empty cells also.for getting the empty cells which is also there in particular column or row what function i should use??

16

Can you tell me scenario…it must take empty cells but I don’t know why it is not taking that empty one…By adding write-line or message box check if empty cells are reading or not…I think it is reading…or if possible share excels or snap …

17

image
image1366×768 81.7 KB

This is the scenario…if i put "DatatableVariable.Rows.COUNT " it is displaying the value as 6 not as 7

18

Counter start from 0 so that it is correct and reading empty also

19

counter is starting from 0 only.can you give me a sample code for this excel

20

Main.xaml (5.9 KB)