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:


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:



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!


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!

10 views