How to get total number of filled rows in excel?

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

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

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

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

Thanks. This worked.

1 Like

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.

@Manoj_prabhakar_R

Check this feed

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.

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

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

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”

2 Likes

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

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.

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.

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?

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

image

Hi @fairymemay

You can check this post,

Thanks
Latika

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#