web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Gallery / Power Automate Cookbook / Delete multiple consecutive...

Delete multiple consecutive and non-consecutive Excel columns via Power Automate Desktop

Views (2171)
VJR Profile Picture Posted by VJR 7,635

 

  • Problem statement:

- By default, Power Automate Desktop is only able to delete one Excel column at a time.

- Below is a Subflow to delete multiple consecutive or non-consecutive columns all at the same time, either by using       

   column alphabets or index numbers or both.

- No loops used to iterate through the Excel columns
- Developer can pass just the columns s/he wants to delete
- Deletes all specified columns in a single shot and not one by one

- Step by step explanation along with the Desktop flow attached below

 

- Developer can pass the columns either as alphabets

   Eg; A:A,C:C,H:H,K:O,Q:U

 

- or Column numbers
   Eg; 1, 3, 8, 11:15, 17:21

 

- with and without colon both in the same pass
   Eg; 1:1, 3, 8, 11:15, 17:21


- or mix of alphabets, numbers, single range, multiple ranges
  1:1, 3, D, F:G, 11:15, 17:21

 

- Can pass as variables too
   %var1%:%var2%,%var3%
   Notice the comma and colon

 

i) Assuming the input file to be as follows and we would like to delete consecutive and non-consecutive columns all in the same pass. 

 

VJR_0-1679805535630.png

 

ii) Follow the numbering

 

VJR_3-1679806481145.png

 

 

 

1: Set the full path of the Excel file from which to delete the columns

2: Set the columns you would like to delete

    Examples given in the Comments section of Line #5 of the code OR at the top of this post.

    I have given a combination of column index, range of alphabets, single alphabet, non-consecutive columns (Refer yellow      highlighted)

3: Pass the name of the Worksheet from which you want to delete the columns

    You can add your own validation to check if the sheet exists.

 

 

iii) Follow the numbering

 

VJR_2-1679806145781.png

 

        

          You don't need to do anything in the below steps. They are just for explanation.

           4: The sheet that you want the columns to be deleted will be activated if its not already.

                This is an optional action. You can delete it if not required.

 

           5: This step will call a Subflow which has a vbscript written to delete the passed columns.

               You will be able to see the code in the vbscript when you download the desktop flow attached to this page.

 

           6: Saves the Excel before closing.

 

You can perform any of the other Excel operations before or after deleting the columns without closing and re-opening the file.

 

 

iv) Input and final output after running the Flow

 

VJR_5-1679807357606.png

 

 

VJR_4-1679807321359.png

 

 

  • How to do the same for Rows is given in the post here

Delete multiple consecutive and non-consecutive Ex... - Power Platform Community (microsoft.com)

 

  • Also contains instructions on how to copy-paste the attached desktop flow files into Power Automate Desktop.

 

v) Refer the comments in the Flow, make changes to parameters and Run the Main flow.

    Make sure to run it against a copy of your Excel at first and not an original Excel file.

 

 

Delete_Excel_Colu...

Categories:

 Desktop flows

Comments

  • bhav2345 Profile Picture bhav2345
    Posted at
    Delete multiple consecutive and non-consecutive Excel columns via Power Automate Desktop

    Thanks @VJR . You are amazing. Keep up the good work.

  • VJR Profile Picture VJR 7,635
    Posted at
    Delete multiple consecutive and non-consecutive Excel columns via Power Automate Desktop

    Hi @bhav2345 ,

     

    It could be because of the version difference I wrote the flow in and you might be currently having.

    I am currently having 2.39.

     

    Microsoft has made many changes.

    Below is the vbscript which you can copy paste inside the "Run vbscript" action.

    Also use the Main.txt.

     

    Dim RangeToUse, arr, i, ReturnName, colonSplitArr, firstSplit, secondSplit


arr = Split("%Cols_To_Delete%", ",")
RangeToUse = ""

For i = 0 To UBound(arr)

	
	 If (InStr(arr(i), ":")) > 0 Then 'If colon : was already added by the developer eg; A:A or D:E or 1:1
	 
 
		 colonSplitArr = Split(arr(i), ":")
		 firstSplit = Trim(colonSplitArr(0))
		 secondSplit = Trim(colonSplitArr(1))
		 
		 
		 If (IsNumeric(firstSplit) = True) Then 'convert column numbers to alphabets
		 
		 firstSplit = Split(GetObject("%ExcelFilePath%").Worksheets("%WorksheetName%").Cells(, CInt(firstSplit)).Address, "$")(1) 
		 
		 Else
		 
		 firstSplit = Trim(colonSplitArr(0))
		 
		 End If
	 
		 If (IsNumeric(secondSplit) = True) Then 'convert column numbers to alphabets
		 
		 secondSplit = Split(GetObject("%ExcelFilePath%").Worksheets("%WorksheetName%").Cells(, CInt(secondSplit)).Address, "$")(1)
		 
		 Else
		 
		 secondSplit = Trim(colonSplitArr(1))
		 
		 End If
		 
		 RangeToUse = RangeToUse & firstSplit & ":" & secondSplit & ","
	 
 
	 
	 Else 'else add colon : because the EntireColumn.Delete syntax takes colon as a range
	 
		 If (IsNumeric(Trim(arr(i))) = True) Then
		 ReturnName = Split(GetObject("%ExcelFilePath%").Worksheets("%WorksheetName%").Cells(, CInt(Trim(arr(i)))).Address, "$")(1)
		 RangeToUse = RangeToUse & ReturnName & ":" & ReturnName & ","
		 
		 Else
		 RangeToUse = RangeToUse & Trim(arr(i)) & ":" & Trim(arr(i)) & ","
		 End If
 
	 End If
	
 
Next

RangeToUse = Left(RangeToUse, Len(RangeToUse) - 1) 'to remove the trailing comma


'Uncomment the below line for testing which columns are considered for deletion
'MsgBox "FINAL range: " & RangeToUse


'Uncomment the below line if you would like to SELECT the columns at first and then DELETE
'GetObject("%ExcelFilePath%").Worksheets("%WorksheetName%").Range(RangeToUse).EntireColumn.Select

GetObject("%ExcelFilePath%").Worksheets("%WorksheetName%").Range(RangeToUse).EntireColumn.Delete 'this is actual line that does the deleting
GetObject("%ExcelFilePath%").Worksheets("%WorksheetName%").Range("A1").select 'to place the focus on 1st cell of the sheet after deleting

'The Excel file will be left OPEN and UNSAVED by the vbscript so that you can continue to perform other Excel operations.
'Save and Close the Excel file using the Excel actions of Power Automate Desktop as shown in last line of the Main tab.

     

  • bhav2345 Profile Picture bhav2345
    Posted at
    Delete multiple consecutive and non-consecutive Excel columns via Power Automate Desktop

    Hi,

    When i pasted the content of delete_excel_columns in the sub flow, it is throwing error "Module 'Scripting' or action 'RunVBScript' wasn't found".

  • knowak Profile Picture knowak 7
    Posted at
    Delete multiple consecutive and non-consecutive Excel columns via Power Automate Desktop

    No problem, your code inspired me and I wrote my own which in the code has specified which columns it deletes. So thank you very much. And from your file I am trying to delete some sample e.g. A or range A-B.

    All points are active.

  • VJR Profile Picture VJR 7,635
    Posted at
    Delete multiple consecutive and non-consecutive Excel columns via Power Automate Desktop

    @knowak, please share which columns you are trying to delete on my file. I will verify it on my side and get back to you. Also share what have you passed to delete those columns. 

  • knowak Profile Picture knowak 7
    Posted at
    Delete multiple consecutive and non-consecutive Excel columns via Power Automate Desktop

    The VB script passes, the flow comes to an end and yet in my case it doesn't delete the columns finically. I even tried on your file.