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
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Top Solution Authors
Top Kudoed Authors
Users online (1,428)