cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Davis_C
Frequent Visitor

Iteration on Excel file - Loop not stopping

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:

 

Excel01.png

 

Here is my loop:

 

Excel02.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ankesh_49
Super User
Super User

3 REPLIES 3
Ankesh_49
Super User
Super User

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

it should work 

PhilipTreacy
Resolver I
Resolver I

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

 

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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Top Solution Authors
Top Kudoed Authors
Users online (3,079)