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

Get First Free Row on Column from Excel on Worksheet

 

TonyTony_1-1646098820248.png

Hope the image can load well enough to understand problem.

 

1: Launch Excel %ExcelInstance%
2: Get First Free Row On Column from Excel Worksheet. Column 1 has empty cells in the rows of data.

3: Expected Result: First Free Row = 3. Result Given = 15.


Anyone have workarounds that can get me the result of 3 until this problem is fixed from the power automate team?

Other tags to help future searchers of this problem:
Gaps in the rows of data, with empty cells, missing cells, seems to be searching incorrectly.

1 ACCEPTED SOLUTION

Accepted Solutions

You can make suggestions here

View solution in original post

3 REPLIES 3
MichaelAnnis
Super User
Super User

The first free row/column looks at the end of the known range, not at populated cells.

 

the known range is the same as if you were to press {Control}({End}) .  It is that cell that is considered the end of the known range and first free column/row, is the column and row after that cell. 

Here is a workaround. It has two assumptions. Assumption 1, there is no missing data in column A. Assumption 2, there is no missing data in the last row. 

 

Select cells A1

Send Keys {Control}({LShiftKey}({Down})))

Send Keys {Control}({LShiftKey}({Right})))

 

Get selected range:

DE17552F-CEC8-43D4-9F5B-57A752089502.png

 

If the two assumptions are correct, this will get you what you need. If only assumption 1 is correct, but the number of columns doesn’t change, then use this to get your last row, and use the original method (first free column/row) to get your first free column. Notice, this workaround will give you the %LastRowIndex%, so the first free row would be %LastRowIndex + 1%

Good luck!

 

TonyTony
Frequent Visitor

Thank you Michael, I may reference this at a later date.
For now I have found that pasting this formula in column B

=MIN(IF(A1:A15="",ROW(A1:A15)))

produces the correct value of 3. Then now I can use the power automate "Read from excel worksheet" to read the number of that cell.

This is great because as my bot populates data into those missing cells, the number will dynamically update as the excel sheet gets updated.

Is there a way I can suggest this be implemented as an excel action? 

Or perhaps there is an even better, more optimal solution I don't know about?

Anyway, thank you for answering my question @MichaelAnnis 

You can make suggestions here

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

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.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Kudoed Authors
Users online (4,570)