Removing Duplicates (Excel)

[Moonscarlet]

Hello,

I am trying to remove duplicates from two columns but I haven't been able to do it properly in my script.

I tried using ".RemoveDuplicates()", but if I put:

$Workbook.Sheets(1).Range("A1:B50").RemoveDuplicates(1)

or

$Workbook.Sheets(1).Range("A1:B50").RemoveDuplicates(2)

This won't give me the correct results as I want to remove the duplicates if both columns are found again, after recording the macro that's what I get in VBA:

ActiveSheet.Range("$A$1:$B$50").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

But I don't know how to put that "Array(1, 2)" without getting errors.

Appreciate your help.

[water]

Global $aColumns = [1, 2]
$Workbook.Sheets(1).Range("A1:B50").RemoveDuplicates($aColumns)

 

[Moonscarlet]

13 minutes ago, water said:

Global $aColumns = [1, 2]
$Workbook.Sheets(1).Range("A1:B50").RemoveDuplicates($aColumns)

 

Thanks but this also gave me an error:

"The requested action with this object has failed."

[water]

Could you please add a COM error handler (please check the help file for ObjEvent) so we get more detailed error information?

[Moonscarlet]

2 hours ago, water said:

Could you please add a COM error handler (please check the help file for ObjEvent) so we get more detailed error information?

You mean like this?

"COM Error intercepted !
    err.number is:         0x80020009
    err.windescription:    Exception occurred.

    err.description is:     
    err.source is:         
    err.helpfile is:     
    err.helpcontext is:     0
    err.lastdllerror is:     0
    err.scriptline is:     701
    err.retcode is:     0x80070057"

[water]

Correct.
Unfortunately the returned information is not very helpful
I will do some testing the next few days and hope to come back with a solution.

[Moonscarlet]

1 minute ago, water said:

Correct.
Unfortunately the returned information is not very helpful
I will do some testing the next few days and hope to come back with a solution.

Much Appreciated

[water]

This works great with Excel 2016:

#include <Excel.au3>
Global $aData[][] = [[1,2],[1,2],[2,3],[2,3]]
Global $aColumns = [1, 2]
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, 1, $aData)
$oWorkbook.Sheets(1).Range("A1:B50").RemoveDuplicates($aColumns)

 

[Moonscarlet]

26 minutes ago, water said:

This works great with Excel 2016:

#include <Excel.au3>
Global $aData[][] = [[1,2],[1,2],[2,3],[2,3]]
Global $aColumns = [1, 2]
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, 1, $aData)
$oWorkbook.Sheets(1).Range("A1:B50").RemoveDuplicates($aColumns)

 

This is weird, it does work, even though it's the same as what you told me to use the other day!

I don't know why I kept getting that error then.

Thanks a lot for helping me get this to work.

[water]

Did you name your workbook variable $oWorkbook or $Workbook?

[Bert]

I just discovered something on my work laptop called "Spreadsheet Compare". 

 

[Moonscarlet]

11 minutes ago, water said:

Did you name your workbook variable $oWorkbook or $Workbook?

It's something else as I have the sheet saved as a variable altogether

  

Global $oExcel = _Excel_Open()
    Global $Prepare= _Excel_BookOpen($oExcel,@ScriptDir&"\Prepare.xlsx",True)
    Global $ProgressSheet= $Prepare.Sheets(2)

Then after a bit of code that fills some columns from another excel sheet:

Global $aColumns = [1, 2]
    $ProgressSheet.Range("S1:T"&$LastCell).RemoveDuplicates($aColumns)

Maybe I messed up the last line by adding another .Sheets(2) by mistake or something , that's the only thing that I can think of that could've happened, I don't remember.

 

Edited December 22, 2017 by Moonscarlet