Deleting multiple Excel Columns in one go

[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

[momlo]

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.

[Kaif_Siddique · Accepted solution]

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

[bubban]

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()

[Kaif_Siddique]

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

[Zy_Sin]

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")

[Kaif_Siddique]

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

[rifij]

@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

[JeffreyDSmith]

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.

[rifij]

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