Find a cell in Excel and counting how many rows of data there are

Hi all. I have this Spreadsheet with over 500 tabs (sheets)

At each tab, I must find a cell with the value “N°.:”

Suppose at sheet “245”, UiPath finds “N°.:” at N41

Up to this point I was able to do it.

Now I must count the rows of data inserted under N41, starting at N42.

So I have this variable (VarNum) that should be assigned readings at N42, N43, N44… until there is a blank cell.

-While: Not string.IsNullOrWhiteSpace(VarNum)

Ok, here is my problem. I have N41 as the cell where I found the value. How do I increase only the row of the cell?

In theory, I want to have a DO WHILE where I read cell N42, add +1 to the row, assigning to the variable cell the same value +1 row, thus becoming N43, then check if the value is empty. If it’s not empty, add one more so it becomes N44.

All the while, I count the number of loops (let’s say, 4 rows of data), so I can use that number to create a data table

Because your excel file is huge, do the operations inside the file scope by creating a macro to imitate the search.
This will make the processing faster.

1 Like

Thanks Madhavi, but the processing speed is not important to me.

Right now the most important thing I need to know how to find a cell (column+row), then transfer that row number (alone) to a variable, so I can check how many rows of data I have below that cell.

Thus I find the “N°.:” at N41.

How to get that “41” out of the variable storing N41, so I can use 41 as a counter in a loop?

I suppose I could WRITE that N41 in a Excel cell, and the next cell having a formula to split column (N) from row (41) and then a UiPath Activity copying those two values to UiPath.

That would not be hard, but that’s quite a work around from something that’s probably easy to do (if you know how) in UiPath.

I did this in this way:
testeExcel.xaml (9.5 KB)

When you get the N41 range, just use Read range again (with N41 as range) to read the file without headers and you can use loop on it either for each row or while loop to check blank values in first column with counter variable so you will how many rows are there after N41.

Once you get the number you can use anyway you want.

Thanks all, but I found that having a special tab on Excel perform some operations to UiPath and then reading the results into UiPath variables is easier.

Let’s say I got N41 as a result.
I paste it into a cell of the calculations tab in Excel, where one cell checks the number of characters (3), another finds the first character with a number (position 2), then a Left and Right formulas split N57 into N and 57.

Later I need to create a range between BX:PY. As I find working with variables in UiPath quite messy, I paste the X and Y into Excel (ex: 45 and 67) and a concatenate formula transforms those into B45:P67.