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!