You’re offline. This is a read only version of the page.
7
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
@JeffreyDSmith appreciate you and you comment. I get it when you explained it like that.
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.
@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
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
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") |
Excellent!... That's better and simple.
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()
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
Share
Report
All responses (
Answers (
