How to Merge Cells with the same value in excel UiPath

While working with excel automation we will use excel as an output file where it stores the output data and shares it across to all the business users.

Let’s consider a scenario where the column names for some columns are consecutive and the same value then we may want to merge all those cells and make it a unique cell before sharing the final excel to business users.

In this article, let’s see how to merge cells with the same value in excel using VBA in UiPath.

Let’s jump into the practical implementation of this task:

Step1: The input we are using for this example is shown below:

From the input, we can see that Excel has three columns named the same as ‘From’ and the other two columns as ‘To’. It needs to merge them as a single column.

Step2: Drag and Drop an excel application scope activity from the activities panel to the designer panel as shown below:

Step3: Drag and Drop Invoke VBA activity from activities panel to designer panel inside the excel application scope as shown below:

The required parameters for Invoke VBA Activity are shown below:

Here:

• CodeFilePath – Provide the file path where you have the VBA code.
• EntryMethodName – Provide the Method name that is used in the code.
• EntryMethodParameters – If you have any parameters then provie them here (in this example we need to pass ending column name).
• OutputValue – Provide a variable that holds the output value of VBA code.

The VBA code to insert an image into an excel sheet is shown below:

Sub MergeCells(colIndex)

  Set myDocument= Worksheets(1)
  Dim rngMerge As Range
  Dim cell As Range

  Application.DisplayAlerts=False
  Set rngMerge=myDocument.Range("A1:"+colIndex)
  myDocument.Range("A1:"+colIndex).MergeCells= False
  myDocument.Range("A1:"+colIndex).HorizontalAlignment=xlCenter

MergeAgain:
  For Each cell In rngMerge
    If cell.Value=cell.Offset(0,1).Value And IsEmpty(cell)=False Then
	Range(cell, cell.Offset(0,1)).Merge
	GoTo MergeAgain
    End If
    Next

End Sub

• colIndex – This is the column index name till where it needs to merge.
• Worksheets(1) – It is the first sheet of the excel. If you want to do this for second sheet then make it as worksheets(2).

Step4: The output of the above sequence is shown below:

Happy Learning!