Answered

Get First Free Row on Column from Excel on Worksheet

(0) Share

Report

Posted on by TonyTony

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.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

MichaelAnnis 5,727 Moderator on at

Like (2)

Report
Copy link

Link copied!

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:

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!

Was this reply helpful? Yes No
TonyTony 9 on at

Like (1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Verified answer

Highboy 1,187 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

You can make suggestions here

Was this reply helpful? Yes No
Harp_KC 5 on at

Like (0)

Report
Copy link

Link copied!

Great solution, I've tested with good results and plan on using this, Thanks!

Was this reply helpful? Yes No