How to write code for copying the sheet and merge cells with same in the INVOKE CODE activity!

HI @ All

I am trying to use invoke code activity to copy the sheet and give it a different name i.e. Input Sheet for the input and output sheet for the output.

And merge the cells with similar values in a single column.

Can anyone help me regarding this?

Thanks in advance.

@Palaniyappan Can you help me?

1 Like

Can anybody help??

What you think cells with similar values are?

@moenk

image

Linke this!

Do you mean with Invoke VBA Script?

I would do something like this:

Sub Macro1()
'Copy input sheet
Sheets(1).Copy After:=Sheets(1)
Sheets(2).Select
ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

Of course you would have to adapt the range for your specific excel input. See Range.RemoveDuplicates method (Excel) | Microsoft Docs.

Another option is to use the in-built UiPath activities. I think that would be easier?

@mari.svh

Thanks.

What if i want to merge the cells with same values in one column?

I mean, if the entries are always sorted like your illustration you could use UiPath and do:

> Use the Read Range Activity to get the Input sheet as a DataTable dt_input.
> 
> Initialize an empty dt_output
> 
> Initialize an empty DataRow Object called previous_row
> 
> For Each Row in dt_input:
>      If Row == previous_row:
>          previous_row = row
>          continue
>      Else:
>           add row to dt_output
>      previous_row = Row
1 Like

This VBScript removes all duplicates from column 1 and 2 (see Range.RemoveDuplicates method (Excel) | Microsoft Docs). :crazy_face: So if you want to merge the cells with the same values in column C you would write

ActiveSheet.Range(“A1:C100”).RemoveDuplicates Columns:=Array(3), Header:=xlYes

Thanks
@mari.svh

I will try it.

Kindly let know if any further clarification required
Have a great day

Cheers @AryanS