Answered

Delete Top 6 Rows - Delete Column C, E, F, G, H, I - Delete the all the subtotal and the Garbage at the bottom of the file

(0) Share

Report

Posted on by TTC Super User 2024 Season 1

Hi,

I am trying to delete that single file and I am not successful. When I ran the script it deletes the top 6 rows as I expected, but it left the column 31-60 Days and 91+Days. Also, it delete the garbage at the bottom of the file, but it left the grand total. I tried everything and I know if the script correct. Is anyone have a better idea, can you please share your answer as PAD so I can see the step. I am sharing the file and my script as well and thank you an advance.

*** THE ORIGINAL FILE IS AN .CSV EXTENSION AND I NEED TO SAVE AS .XLSX

I try to drag the entire file, but the forum is not supported .csv file.

1. THIS IS THE TOP FILE

2. BOTTOM FILE

3. SCRIPT

AFTER SCRIPT RAN - TOP FILE

BOTTOM FILE

Categories:

Power Automate Desktop

I have the same question (0)

All responses (12)

Answers (2)

Sort by

Verified answer

Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @TTC

For column you made a logical mistake, using the action to delete column, when you delete a C column using the action, D column will moved to C column section, so all columns from C will move to their previous column
like D >C
E >D
F>E
etc.........

So when you need to delete the E column as per original excel file, its position have changed to D column, so instead of delete Column E, you have to use delete Column D instead. So likewise you have to change that.

So in your case it would be

For rows, try this approach

Hope this helps

Mark it as solution if it resolves your query 🙂

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Nived,
Thank you so much. All the columns are gone except the Grand total is still showing.

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @TTC

If you look at your script (power automate code), you are first finding first free rows/column of an active worksheet and then making R&L Carriers as active worksheet so before making R&L Carriers as active worksheet some other sheet in workbook will be considered as active worksheet which have given their free row instead of R&L Carriers worksheet

So place set active worksheet action before get first free rows/column of excel action so that it can work correctly
Hope it helps !

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

I did put it before 'Get first free column' and it still the same. I am not sure if this is where you want me to place it in the script.

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Ok @TTC

May i know what is the value of FirstFreeRow variable ?
Also are you seeing any rows being deleted ?

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Per my screenshot the only rows that been deleted are the columns. The total at the bottom is still remain.
Below are the first delete with the FirstFreeRow -1 and next one is -3

*** Do you think it because the file in .csv extension is the reason why it won't delete the Grand total at the bottom?

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @TTC

Could you check what is the value of firstfreerow variable ?

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

It look like it takes the first garbage on file and leave the total. This is the original file before everything.

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

The only way I can see the total is deleting, if I used the action 'Delete from Excel worksheet' instead 'Delete row from Excel worksheet' and I have to put the last row by hand. If I use the variable %FirstFreeRow-1% it does not work for me. Something is causing the grand total not deleted at the bottom of the file and I cannot figure what is the root of the issue.

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Nived,
It looks like I got it. I counted from row 444 and up that give me 9 rows up. Then I put -9 on the second action 'Delete row from Excel worksheet' and then I ran the script one last time and now the Grand Total is gone. And it looks like I don't even need the first action %FirstFreeRow-1%. Please let me know if my approach is correct of if you're thinking to do it a different way. Thanks!

Was this reply helpful? Yes No