VBA Inserting an array as columns to remove in excel table

[CRHAAG]

I have an excel table (reportInfo(0)).ListObjects(reportInfo(1)) that I am trying to remove duplicates from. To do this, I need to call out an array with the columns with duplicate values. In this case, I need to remove any rows in which columns 1 and 3 are the same. My code is as follows:

Dim columnsToDuplicate As Variant
columnsToDuplicate = Array(1, 3)

'REMOVE DUPLICATES
table.Range.RemoveDuplicates Columns:=Array(columnsToDuplicate), Header:=xlYes

When I run it this way, I get a type mismatch error. If I change the remove duplicates code to either of the following, it works:

'REMOVE DUPLICATES
table.Range.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes

Or:

'REMOVE DUPLICATES
table.Range.RemoveDuplicates Columns:=Array(columnsToDuplicate(0), columnsToDuplicate(1) ), Header:=xlYes

I can't use the second option because the number of columns that I want to use to remove duplicates will not always be the same.

 

[AlphaFrog]

This is redundant.
Array(columnsToDuplicate)

columnsToDuplicate is already an array. It would be equivalent to saying Array(Array(1,3))

Try this...

'REMOVE DUPLICATES
table.Range.RemoveDuplicates Columns:=columnsToDuplicate, Header:=xlYes

 

[CRHAAG]

This is redundant.
Array(columnsToDuplicate)

columnsToDuplicate is already an array. It would be equivalent to saying Array(Array(1,3))

Try this...

'REMOVE DUPLICATES
table.Range.RemoveDuplicates Columns:=columnsToDuplicate, Header:=xlYes

Thanks, but I think I tried that one already. I still get "Invalid procedure call or argument" on that line when I write it that way.

 

[CRHAAG]

Found it, for some reason columnsToDuplicate needs to be inside ()
table.Range.RemoveDuplicates Columns:=(columnsToDuplicate), Header:=xlYes

 

[Peter_SSs]

I have been able to duplicate what the OP has stated. I do not know why what AlphFrog posted does not work.
.. at least for me and the OP.

@AlphaFrog
Have you been able to actually run what you posted?

 

[AlphaFrog]

@AlphaFrog
Have you been able to actually run what you posted?

No Peter. I only have Excel 2003 which doesn't support the .RemoveDuplicates method.

I don't understand why the parentheses are needed as well.

 

[Peter_SSs]

No Peter. I only have Excel 2003 which doesn't support the .RemoveDuplicates method.

I don't understand why the parentheses are needed as well.

OK, thanks. Yes baffling to me.

 

[Peter_SSs]

RoryA has provided some more information, though we still do not know why AlphaFrog's code from post #2 does not work.
Here are two other methods, as suggested by RoryA, that do also work, though perhaps they raise questions themselves - especially the second one.

1. Declare columnsToDuplicate just as it was in post #1 but then force it to an array with CVar
This is really like using the parentheses as in post #4 (we think)

  Dim columnsToDuplicate As Variant
 
  columnsToDuplicate = Array(1, 3)
  table.Range.RemoveDuplicates Columns:=CVar(columnsToDuplicate), Header:=xlYes

2. Declare columnsToDuplicate as an array - but then leave out the names of the parameters in the RemoveDuplicates line. That seems really weird to me.

  Dim columnsToDuplicate() As Variant
 
  columnsToDuplicate = Array(1, 3)
  table.Range.RemoveDuplicates columnsToDuplicate, xlYes

 

[AlphaFrog]

#2 leave out the names of the parameters
that is bizarre

Does this work?

  Dim columnsToDuplicate As Variant
 
  columnsToDuplicate =(Array(1, 3))
  table.Range.RemoveDuplicates Columns:=columnsToDuplicate, Header:=xlYes

 

[Peter_SSs]

Does this work?

No, the last line of that errors: