Learn how to Unmerge and Duplicate cells in UiPath!

One of the major challenges I have faced while designing solutions is that the Input Data that the bot has to work with is not standardized, especially in Excel Sheets.

Usually, the end-user builds a report which is visually pleasing to the eye but is difficult to be handled programmatically. For example:

Excel

In the above-mentioned sheet, the challenge is that Column 'A' has merged cells that cannot be directly pulled into a Datatable. Today, I am going to show you how to unmerge these cells and duplicate the value in each cell so as to standardize the sheet.

Step 1: Create a Variable( I have used strRange) that stores the Range in which you want to Unmerge and Duplicate. For Example for Column A, use "$A1:$A100"

Step 2 :Use the Invoke VBA Activity to make the changes!

Use the following Sequence:

Uipath

Create the VBA Script and save it as a text file. Paste the following lines in the text file:

Sub UnMergeSameCell(strRange as String)
'Upadateby Extendoffice
Dim Rng As Range, xCell As Range
Set WorkRng = Application.Selection
Set WorkRng = Range(strRange)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.RemovePersonalInformation = False
For Each Rng In WorkRng
    If Rng.MergeCells Then
        With Rng.MergeArea
            .UnMerge
            .Formula = Rng.Formula
        End With
    End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Here is the End result!

Excel

Simple, yet very very Useful in Standardizing Inputs for Bots!

If you enjoyed this article, please take a look at how to compare two addresses that aren't Identical but are inherently the same!