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!