top of page

Learn how to Unmerge and Duplicate cells in UiPath!

Updated: Jan 18, 2022




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
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
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
Excel

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

820 views0 comments

Recent Posts

See All

Comments


bottom of page