Getting row count based on a specific column

[jenn.cruz (JC11)]

Hello,

I’m just starting on my RPA journey and could use some assistance. I need to obtain a row count based on the last cell in a particular column that has data. I know there are various activities I can use for this, but a bit stuck on the argument to use. Any help would be greatly appreciated.

[fernando_zuluaga]

Hi @jenn.cruz

after reading your excel and getting itself into a datatable variable,
you can get the rowcount with the following query

dtVar.AsEnumerable().Select(Function(x) x("YourColumnName").ToString().Trim()).ToArray().Where(Function(y) Not String.IsNullOrEmpty(y)).ToArray().Count

Regards!

[postwick (Paul)]

Read Range into a datatable. Then yourDT.Rows.Count gives you the row count.

[jenn.cruz (JC11)]

Thank you! For “Your Column Name” can I put A or do I need to use the Region title? I was concerned that I may receive an error since the Region is not on the first row.

[fernando_zuluaga]

you should put your column header, in this case Region will be the one

[jenn.cruz (JC11)]

Thank you. This is what I initially did, but it was picking up a total row on row 4999 which I do not want.

[jenn.cruz (JC11)]

I’m receiving the below error. Does it have something to do with the output for read range?

[jenn.cruz (JC11)]

[fernando_zuluaga]

your rowcount variable should be of type Int32 or else add a .ToString() to the code i shared like this

dtVar.AsEnumerable().Select(Function(x) x("YourColumnName").ToString().Trim()).ToArray().Where(Function(y) Not String.IsNullOrEmpty(y)).ToArray().Count.ToString()

Regards!

[jenn.cruz (JC11)]

Thank you! I’m getting closer thanks to you. I received the error below. Would it be beneficial to start my read range at A2?

[fernando_zuluaga]

just make sure to check the addHeaders property in your read range activity

[postwick (Paul)]

That means your Excel file has a bunch of rows with empty values. You can use Filter Data Table to get rid of the empty rows.