topic RE: RemoveDuplicates VB code stage in Product Forum

RemoveDuplicates VB code stage

Derek.Turner — Wed, 25 Sep 2019 15:37:00 GMT

Hello everyone,

I have found a few places in the many forums available where there is some mention of using this VB code in a code stage to remove duplicate lines within an Excel file. This code below works perfectly fine when I only pass it one column to check, but I cannot figure out how to pass multiple columns to be checked.

sheet.Range("A1:E100").RemoveDuplicates(Columns:=1, Header:=1) works great.

I have tried using the following code using curly brackets which I had read was how you should represent an array, but I get the error "Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"

sheet.Range("A1:E100").RemoveDuplicates(Columns:={1,2}, Header:=1)

The following gives me an error of Exception from HRESULT: 0x800A03EC
sheet.Range("A1:E100").RemoveDuplicates(Columns:="1,2", Header:=1)

I know there are other ways to go this via macros, spying Excel etc, but I am trying to build up my own Excel VBO to supplement what is currently missing from the existing one.

Does anyone have any experience with how to fix this? Any help or thoughts would be greatly appreciated.

------------------------------
Derek Turner
Applications Advisor
Celestica
America/Toronto
------------------------------

RE: RemoveDuplicates VB code stage

NagaM — Thu, 26 Sep 2019 12:55:00 GMT

Hi Derek,

Please post sample excel file and VBO here and mean while please check the below code, here Array contains column index where 1 = "Column A", 2 = "Column B" and soon.

Sheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=1

------------------------------
Naga
Associate
Asia/Kolkata
------------------------------

RE: RemoveDuplicates VB code stage

Derek.Turner — Thu, 26 Sep 2019 13:52:00 GMT

Hi Naga,

When using the Array(1,2), I receive a compile error of 'Array' is a type and cannot be used as an expression which is why I was trying to use the {1,2} as the way to express the array values.

Attached is the sample data I am using as well as the code stage text.

------------------------------
Derek Turner
Applications Advisor
Celestica
America/Toronto
------------------------------