Iteration on Excel file - Loop not stopping

[Davis_C]

Hello,

I have an excel file which my loop doesn't stop on the last result/row.

This file will have a different number of rows filled everyday, so its not static:

Here is my loop:

After it reads the last row with data, it will keep trying to populate the browser (above) with empty data from empty rows.

My 2 questions:

1-How to make it stop on the last row with value, and;

2-How to clean all rows on excel.

*All rows with data must be erased once its done, because I faced the following:

Yesterday, 20 lines were written on the excel.

Today there was only 10 lines to be written, so it overwrote the first 10 lines, but the other 10 lines was the results from yesterday.

Thanks for your help in advance.

[Ankesh_49 · Accepted solution]

@Davis_C  Please refer this https://youtu.be/1WezrCQlpBY 

it should work 

[PhilipTreacy]

Hi @Davis_C 

Excel treats cells with formatting as active i.e. they contain something.  You have a column of dates, so I suspect that the format for those cells is one of the Date formats in Excel.

Even if you delete the data in those cells from inside Excel, the formatting (date) remains and Excel treats those cells as active.

Open the Excel file and press CTRL+END, where does the active cell go to? Check that cell's format.  Even if you get cells like this back to General format the problem remains.

Deleting the range of cells using PA will remove formatting too, but I guess you are not deleting all cells containing formatting.

If you open the file and use CTRL+END you'll see the last active cell and know what row and column you need to delete up to.

If you don't do this, using Get first free column/row from Excel worksheet will not work as you expect

regards

Phil

[Davis_C]

Thank you! It really worked.

If someone is having the same issue, you need to define the ending as FirstFreeRow/Column -1

See Ankesh Youtube link