How can i get range of excel table

(0) Share

Report

Posted on by Crouching-SD

There is a table of excel worksheet.

What I want to do is copy 'B' column's data to 'A'column

Currently : Activate B2 and check,

if B2 is empty then +1 rowindex

else copy and paste it. and make it a loop until lastrowindex

but It takes more than 4minutes.

How can I get range of table like B2, B7~B12, B14~Bxx, ...

or is there a better way to solve it?

Categories:

Power Automate Desktop

All responses (8)

Answers (0)

Sort by

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
HI @Crouching-SD,

Here is a simple demo, please check for details:

items('Apply_to_each_2')?['__PowerAppsId__'] items('Apply_to_each_2')?['Ex']

Here is the testing result:
Crouching-SD 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for reply
I'm sorry, I use POWER AUTOMATE DESKTOP... I didn't mentioned it.
Could you please transltate it for POWER AUTOMATE DESKTOP?
Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
You will need to do a loop anyway. But a faster way that involves less reading from Excel would be to do the following steps:

Get first free column/row from Excel worksheet - this will give you the range you need to read

Read from Excel worksheet - set it to read a range of cells starting at A1 and ending at row %FirstFreeRow-1%. This will result in a data table called %ExcelData%

Loop from 0 to %ExcelData.RowsCount-1% iterating by 1.

If %ExcelData[LoopIndex][1]% (the 1 here indicates column 2, as indexes are 0-based) is empty

Next loop - this will skip further actions and move on to the next row

End if

Write to Excel worksheet - write %ExcelData[LoopIndex][1]% to column A row %LoopIndex+1% (again, because indexes are 0-based)

End loop

This should be faster, because you will only read the data from Excel once and then write to Excel when needed.

Try it and see if this works for you.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
ThomasFonn 273 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Crouching-SD
You could load the entire sheet (or just those two columns) into a data table in Power Automate
Loop it as a variable rather than going over the UI-elements, as that is much faster.
Then at the end, print that array and overwrite what you had in the excel

I tried making a demo real quick and it just takes a few milliseconds
SET Null TO $'''%''%'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\my.username\\Desktop\\test overlap.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: $'''B''' FirstFreeRowOnColumn=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: $'''B''' EndRow: FirstFreeRow ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
LOOP LoopIndex FROM 1 TO FirstFreeRow - 1 STEP 1
    IF IsNotEmpty(ExcelData[LoopIndex][1]) THEN
        SET ExcelData[LoopIndex][0] TO ExcelData[LoopIndex][1]
        SET ExcelData[LoopIndex][1] TO null
    END
END
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: ExcelData Column: 1 Row: 1

You could copy and paste this into your own solution but change the file path to match your desired file paths
Crouching-SD 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for reply
It works and 50% less time, Thanks.

Actually, I want to get range of between empty cell of B column. and copy and paste to A column.
Because It can save more time.

Is there a way to specify it as a range?
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, you could do it, but it wouldn't make things much faster, because you would need to build extra nested loops inside your loop to find the end of the range, then use Copy cells from Excel worksheet and then Paste cells to Excel worksheet. While it may seem like it is faster this way, I highly doubt it will in fact be much faster, as you will be doing more looping, as well as actually copying and pasting, which is slower than reading the data once and then simply writing to the first column from the data table.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
Crouching-SD 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for reply
It works and 50% less time, Thanks.

Actually, I want to get range of between empty cell of B column. and copy and paste to A column.
Because It can save more time.

Is there a way to specify it as a range?
ThomasFonn 273 on at

Like
a
(1)

Report
Copy link

Link copied!

In case we were missing something;
If you are running it from the developer/debugging view, there's a built in delay for every action,
at the bottom of the screen you can change that delay to be shorter.
But if you really want it to go fast, you should run the flow from the console