topic Re: Get First Free Row on Column from Excel on Worksheet in Power Automate Desktop
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1481345#M11900
<P>Thank you Michael, I may reference this at a later date.<BR />For now I have found that pasting this formula in column B</P><LI-CODE lang="markup">=MIN(IF(A1:A15="",ROW(A1:A15)))</LI-CODE><P>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.<BR /><BR />This is great because as my bot populates data into those missing cells, the number will dynamically update as the excel sheet gets updated.<BR /><BR />Is there a way I can suggest this be implemented as an excel action? <BR /><BR />Or perhaps there is an even better, more optimal solution I don't know about?<BR /><BR />Anyway, thank you for answering my question <LI-USER uid="270415"></LI-USER> </P>Tue, 01 Mar 2022 07:57:57 GMTTonyTony2022-03-01T07:57:57ZGet First Free Row on Column from Excel on Worksheet
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1480984#M11883
<P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="TonyTony_1-1646098820248.png" style="width: 400px;"><img src="https://powerusers.microsoft.com/t5/image/serverpage/image-id/420059i5CC9AFD3E74CE8F7/image-size/medium/is-moderation-mode/true?v=v2&px=400" role="button" title="TonyTony_1-1646098820248.png" alt="TonyTony_1-1646098820248.png" /></span></P><P>Hope the image can load well enough to understand problem.</P><P> </P><P>1: Launch Excel %ExcelInstance%<BR />2: Get First Free Row On Column from Excel Worksheet. Column 1 has empty cells in the rows of data.</P><P>3: Expected Result: First Free Row = 3. Result Given = 15.</P><P><BR />Anyone have workarounds that can get me the result of 3 until this problem is fixed from the power automate team?<BR /><BR />Other tags to help future searchers of this problem:<BR />Gaps in the rows of data, with empty cells, missing cells, seems to be searching incorrectly.</P>Tue, 01 Mar 2022 01:46:56 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1480984#M11883TonyTony2022-03-01T01:46:56ZRe: Get First Free Row on Column from Excel on Worksheet
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1481304#M11899
<P>The first free row/column looks at the end of the known range, not at populated cells.</P>
<P> </P>
<P>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. <BR /><BR /></P>
<P>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. </P>
<P> </P>
<P>Select cells A1</P>
<P>Send Keys {Control}({LShiftKey}({Down})))</P>
<P><SPAN>Send Keys {Control}({LShiftKey}({Right})))</SPAN></P>
<P> </P>
<P><SPAN>Get selected range:</SPAN></P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="DE17552F-CEC8-43D4-9F5B-57A752089502.png" style="width: 1284px;"><img src="https://powerusers.microsoft.com/t5/image/serverpage/image-id/420200iE3FF1BA634AEB3F0/image-size/medium/is-moderation-mode/true?v=v2&px=400" role="button" title="DE17552F-CEC8-43D4-9F5B-57A752089502.png" alt="DE17552F-CEC8-43D4-9F5B-57A752089502.png" /></span></P>
<P> </P>
<P> </P>
<P>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%<BR /><BR /></P>
<P>Good luck!</P>
<P> </P>Tue, 01 Mar 2022 07:23:27 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1481304#M11899MichaelAnnis2022-03-01T07:23:27ZRe: Get First Free Row on Column from Excel on Worksheet
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1481345#M11900
<P>Thank you Michael, I may reference this at a later date.<BR />For now I have found that pasting this formula in column B</P><LI-CODE lang="markup">=MIN(IF(A1:A15="",ROW(A1:A15)))</LI-CODE><P>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.<BR /><BR />This is great because as my bot populates data into those missing cells, the number will dynamically update as the excel sheet gets updated.<BR /><BR />Is there a way I can suggest this be implemented as an excel action? <BR /><BR />Or perhaps there is an even better, more optimal solution I don't know about?<BR /><BR />Anyway, thank you for answering my question <LI-USER uid="270415"></LI-USER> </P>Tue, 01 Mar 2022 07:57:57 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1481345#M11900TonyTony2022-03-01T07:57:57ZRe: Get First Free Row on Column from Excel on Worksheet
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1481515#M11903
<P>You can make suggestions <A href="https://powerusers.microsoft.com/t5/Power-Automate-Ideas/idb-p/MPAIdeashttps://powerusers.microsoft.com/t5/Power-Automate-Ideas/idb-p/MPAIdeas" target="_self">here</A></P>Tue, 01 Mar 2022 10:12:27 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Get-First-Free-Row-on-Column-from-Excel-on-Worksheet/m-p/1481515#M11903Highboy2022-03-01T10:12:27Z