Answered

Deleting multiple Excel Columns in one go

(1) Share

Report

Posted on by bubban

Hi,

I have successfully used the "Delete column from Excel Worksheet" action, in Power Automate Desktop. However, if I would like to remove multiple columns at the same time it it does not work. For instance, I have an excel sheet with 50 columns and I would like to delete columns 5, 28 and 47. Can that be done without having to use the action 3 times in the flow?

Of course using it 3 times is not an issue but if I have to delete 10's of columns, then it starts becoming a hassle.

Thanks

Categories:

Power Automate Desktop

I have the same question (0)

All responses (10)

Answers (1)

momlo 1,527 Super User 2024 Season 1 on at

Like (0)

Report

Hi @bubban
You can use VBA action. So it will be 1 action in PAD. and within the code you delete as many columns as you need.

Was this reply helpful? Yes No

Verified answer

Kaif_Siddique

2,108 Super User 2024 Season 1 on at

Like (2)

Report

Hi @bubban ,

Use VBScript for deleting multiple excel columns in one go.

Set objExcel = CreateObject("Excel.Application")
Set wb = objExcel.Workbooks.Open("File Path")
objExcel.Visible = True
wb.Worksheets("Sheet1").Range("A:A").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("D:D").EntireColumn.Delete
wb.Save
wb.Close

If I have answered your question, please mark my post as Solved.

If you like my response, please give it a Thumbs Up.

Regards

Kaif

Was this reply helpful? Yes No

bubban 9 on at

Like (1)

Report

Thanks Kaif. Unfortunately it did not work using VBScript for some reason but it worked using PowerShell instead.

By using this script, it performed as per what you were proposing:

# Delete columns CM to CK (inclusive)
$worksheet.Range("CM1:CK1").EntireColumn.Delete()

Was this reply helpful? Yes No
Kaif_Siddique 2,108 Super User 2024 Season 1 on at

Like (0)

Report

Excellent!... That's better and simple.

Was this reply helpful? Yes No
Zy_Sin 79 on at

Like (0)

Report

hi @Kaif_Siddique

If i already have my excel opened.
how do i write the VBscript?
can i Set wb without the "Open"?
Set objExcel = CreateObject("Excel.Application")
Set wb = objExcel.Workbooks.Open("File Path")

Was this reply helpful? Yes No
Kaif_Siddique 2,108 Super User 2024 Season 1 on at

Like (0)

Report

Hi @Zy_Sin ,

Try the below code to read value from already opened excel.

Dim objApp, wbBook, strVal
Set wbBook = GetObject("YourFilePath")
Set objApp = wbBook.Application
strVal = objApp.Worksheets(1).Cells(1, 1).Value
MsgBox (strVal)

Hope this will help you.

Regards

Kaif

Was this reply helpful? Yes No
rifij 17 on at

Like (0)

Report

@Zy_Sin what would the whole VB Script be for an already opened excel file in which columns need to be deleted from? Need to do a likewise and like above having multiple delete from excel columns seems overwhelming. I've tried editing your above code with the below and have something like this, so let me know if im on the right track? The ExcelInstance in code is currently holding the path for the file location in PAD:

Set objExcel = CreateObject("Excel.Application")
Set wb = objExcel.Workbooks.Open("ExcelInstance")
objExcel.Visible = True
wb.Worksheets("Sheet1").Range("G:I").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("N:O").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("Q:Q").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("V:V").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("AB:AC").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("AG:AH").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("AM:AN").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("AQ:AS").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("AU:AU").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("AW:AW").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("AZ:BA").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("BC:BS").EntireColumn.Delete
wb.Save
wb.Close

Was this reply helpful? Yes No
JeffreyDSmith 69 on at

Like (1)

Report

Unless I am misunderstanding your question, you will probably get better results by deleting from last to first so that the item numbering is not affected. For example, when deleting item {3,6,10}, the deletion of item {3} will change the numbering of items {6,10} to {5,9}, and so on. If you instead delete item {10} first, then the remaining items {3,6} numbers are not affected for the next pass. This applies to looping through a list of item numders or a list of deletion statements. Delete from the last item to the first item to preserve the item numbering.

Was this reply helpful? Yes No
rifij 17 on at

Like (0)

Report

@JeffreyDSmith appreciate you and you comment. I get it when you explained it like that.

Was this reply helpful? Yes No
Suggested answer

SA_VOH 210 on at

Like (0)

Report

Hi,

I was facing a similar problem and then came to this Post in Power Platform Community.

I think reply of @JeffreyDSmith is one of the best solutions, wherein instead of writing Office Scripts & VB Scripts, we can simply solve the problem using PAD UI -Drag & drop.

It solved my problem of adjacent columns only. Th ecsreenshot is below:-

But how to delete the columns while entering a loop when the columns are not adjacent. What should i enter in "Increment by".

@JeffreyDSmith may please look into this problem and may provide solution to the same.

Further, i will request @JeffreyDSmith, if you could possibly take up the matter with Microsoft Power Platform Designed team, to provide the options of deleting multiple columns in one go, otherwise we have to do a workaround or use Scripts.

Thanks.

Was this reply helpful? Yes No