Read Range - get range of rows where only formulas are used in cells

Help Activities
, ,
1

Hello All,

need help.

I am using Read range activity to get rows count.
In my sheet until row# = 208, values + formulas are present hence read range is giving me rowCount = 208 but…

Actually, sheet has row from 209 to 300 with only formulas in cells. Also, value of these cells is blank ( as per formula’s output).
I want to identify the actual range which in my case is “300” and not 208.

Any ideas please?

2

@shubhamkumar.tiwari

Can you show what are you using or how are you using it?

Cheers

3

maxRow = dt.rows.count

4

I am trying your another solution from other post,

But as soon as i type Excel. and press Ctrl+Space UiPath hangs for sometime.
I tried to type Excel.Sheet but there also after “Excel.” as soon as I type S it hangs again.

5

@shubhamkumar.tiwari

The hangin gissue might be because of your ram or less hard disk space

But coming to the count…how did you happen to reqd the data?

Did you happen to try with read range workbook or excel? And did you try with preserve format or display format options?

Cheers

6
  1. I don’t think RAM or les disk space is the reason since working in office system it has quiet good configuration.
  2. Cannot go with Read range workbook since it is .xlsb
  3. Tried with Preserve format and its takes forever to read file. Also, still getting count = 208 only.

Did not get this point.!!

7

@shubhamkumar.tiwari

Can you share a sample file here?

Cheers

8

I am sorry, that is not possible. Not even screenshot.

for you to understand:

In Sheet1 - I can see 300 rows in total.

from row 1 - 208, cells contain values + formulas
from row 209 - 300, cells contain only formulas.

I used read range in excel application scope, maxRow = dt.rows.count = 208.
It is not considering rows from 209 to 300 in range by identifying or reading them.

9

@shubhamkumar.tiwari

I understand that can you send with dummy data you can remove such data…

Because I tried creating a similar one but I am able to read the empty rows as well

Need to check what is different with the file

Also try to change excel package version and try to use Excel.sheet option that might wokr

Cheers

10

Uploading any kind of file is restricted.

Can you send xaml or screenshot on how you are able to read empty cells also?
Hope you have used some formulas atleast in those cells in last few rows of sheet.

11

Any suggestions on this from anyone?
Even if someone can provide vbscript to open a .xlsx/b/m file, select a sheet and find it used range which will cover above mentioned scenario.

12

Hello,

I have created a similar file.

In this file read range gives rowCount = 30 but from row 31 cells only contain formulas (which I have replicated) and no values (or Empty (“”) due to formula’s result). So instead of 30 I need rowCount = 50 in this case.
Please help.
sample.xlsx (15.1 KB)

13

Tried this but no luck.

14

@ppr Any suggestions?