How to get total number of filled rows in excel?

Help
,
1

I want to get the largest row number t which some value is entered.

1 Like
2

Read the excel data into datatable variable by “Read Range activity”, then “dtTableVariable.Rows.Count” will give you the total number of rows in an datatable.

3 Likes
3

Here we have to enter the range right. Then how will we know till where the value is contained and fix the range?

2 Likes
4

Range is not mandatory , remove the range then entire excel sheet data can be read into data table. Include the range if you need to copy only a portion of data from the sheet.

2 Likes
5

Thanks. This worked.

1 Like
6

Have any idea on how to read hidden row values from Excel using “Read Range”. Since I can create datatable with only un-hidden rows from excel, so when I try to update the particular cell, the cell value in excel is not equals to the value of datatable.

7

@Manoj_prabhakar_R

Check this feed

8

You can use a counter in a while/do while loop which will iterate through the specified range in excel. Use an if activity and the condition datatable.item(counter).Tostring.equals(“”) . Do all of this inside the excel application scope.

9

Hi
i wanna get the row count with empty values also.If i use this code “dtTableVariable.Rows.Count” i am getting only row count only with values.Help me out of this

10

Sorry for the noob question, but where does “dtTableVariable.Rows.Count” get entered into? The Read Range Activity?

11

No question is a noob question :slight_smile: and use an assign activity , create a generic/integer type variable then just put “dtTableVariable.Rows.Count” in it.

example:

intvar= “dtTableVariable.Rows.Count”

3 Likes
12

Hi @ashutoshkhantwal, thanks for your explanation. this only work for closed excel file, I think. How about read row count in active Excel sheet?

13

Well, I didn’t quite get the meaning of an active sheet, nevertheless you can only read the excel as a datatable/database only even if you use excel application scope.

The formula mentioned above can be used post reading the excel as a datatable.

14

You should try specifying the name of the worksheet when reading in data. Wouldn’t that work?

Regardless, the Excel cannot be read unless it is opened . The mechanism to open it can be visible or invisible to the user, but it has to be opened to be read.

15

ya, this is my problem also when using excel application scope, it’s not allowed me to continue the process with error “Excel Application Scope: Error opening workbook. Make sure Excel is installed.” already tried with uncheck visibility properties and regedit, error still persist. Already post the in another chat.

however, is there any workaround for count the row in excel instead of that you mentioned earlier?

16

@ashutoshkhantwal If I want to count row in column B only.
Please guide me about it.

image

17

Hi @fairymemay

You can check this post,

Thanks
Latika

18

Hi, I believe you want to count the filled cells in a particular column. You can use the LINQ query or you can make use of if else based conditional statement. Both of them have been explained in the link shared by @Latika10011740 .
I would prefer that you use LINQ if you have some knowledge of VB.net/C#

19

Hello everyone,

I tried Read Range but my data is huge. my bot is running for the last 40 min and I have to get the row count multiple time. so this way bot will keep on running for approx 5 hrs. Do we have any other way (a faster way)?
Thanks in advance!!

Sanket Agarwal

20

This seems like a dubious answer. If you know the range before hand, you likely know the row count. In my use case, I do not know how many records the user will pass in, so I can not hard code the range in.