Delete multiple consecutive and non-consecutive Excel columns via Power Automate Desktop 08-02-2023 06:27 AM

[VJR]

• 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. 

ii) Follow the numbering

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

          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

• 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.

[knowak]

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.

[VJR]

@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]

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.

[bhav2345]

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

[VJR]

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]

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