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

[TTC]

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

[Nived_Nambiar · Accepted solution]

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 🙂

[TTC]

Hi Nived,

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

[Nived_Nambiar]

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 !

[TTC]

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.

[Nived_Nambiar]

Ok @TTC 

May i know what is the value of FirstFreeRow variable ?

Also are you seeing any rows being deleted ?

[TTC]

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?

[Nived_Nambiar]

Hi @TTC 

Could you check what is the value of firstfreerow variable ?

[TTC]

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

[TTC]

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.

[TTC]

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!

[Nived_Nambiar · Accepted solution]

Hi @TTC 

Yes now i got it. This was a mistake from my side. actually in your script i haven't noticed you have used delete row at top within the loop

so due to the delete action, row would have shifted up due to which due to which last row has come upwards, so the last row won't be at %FirstFreeRow-1% position, it would be at different position, likewise, same for total part as well.

so if the the %FirstFreeRow -9% works for you, then it would be fine 🙂

[TTC]

Hi Nived,

Thank you very muck for all your help on this.  Yes, this is working fine for me now.

You're a big help and thank you for your patience.