Solved: Excel2007 .RemoveDuplicates

[Paul_Hossler]

Small piece of a larger macro

.RemoveDuplicates wants an array of columns. I recorded a macro and the second version below works with the explicit Array (...) from the recorder

I tried to build an array to pass, and that does not work.

They look like the same thing to me. Can anyone see what the difference is and how to correct / change it?


[vba]
Option Explicit

'the full sub has a declaration like this
'Sub ListRemoveDups(ws As String, ParamArray ColNames())

Sub ListRemoveDups1()
Dim aiColNames(0 To 2) As Variant ' or Long, still doesn't work

aiColNames(0) = 1
aiColNames(1) = 2
aiColNames(2) = 3
'this one doesn't work - Err5 Invalid procedure call of argument
Worksheets("sheet1").Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=aiColNames, Header:=xlYes

'this one does
Worksheets("sheet1").Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub
[/vba]

Since this is part of a large set of macros, I'm sort of constrained to allowing a flexible input with different choices of column names

Thanks

Paul

[Paul_Hossler]

Well, that was interesting

Thanks to some ideas here ...

http://database.ittoolbox.com/groups...ameter-3421497

found this in case it helps anyone figure out a solution:

Instead of VBA's Array function, you should create a new array of Object(), holding integers. It would seem more reasonable to pass it an array of Integer(), but VBA's Array function actually returns an array of Object() holding integers, and this is what the Excel.Range.RemoveDuplicates method expects for the Columns parameter.

link: http://www.xtremevbtalk.com/showthread.php?t12236

It seems like all that I needed to do was to coerce the 'real' array into something that .RemoveDuplicates liked

[vba]
Worksheets("sheet1").Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=(aiColNames), Header:=xlYes
[/vba]

will coerce the array into something acceptable to .RemoveDuplicates

Gotta admit I don't really understand all of the explaination on the other site, but the ( ) corecion seems to work

Paul

[Kenneth Hobs]

When working with arrays named a() for example use:
[VBA]WorksheetFunction.Transpose(a)
'or
WorksheetFunction.Transpose(WorksheetFunction.Transpose(a))[/VBA]

[Paul_Hossler]

Ken -- thanks, I tried both suggestions, but none worked

Did I do Ken-1 and Ken-2 below correctly?

Like I found out, the Paul-2 veriosn is the only way I could get it to work

[VBA]

Option Explicit

'the full sub has a declaration like this
'Sub ListRemoveDups(ws As String, ParamArray ColNames())

Sub ListRemoveDups1()
Dim aiColNames(0 To 2) As Variant ' or Long, still doesn't work

aiColNames(0) = 1
aiColNames(1) = 2
aiColNames(2) = 3

' this one is the original recorded macro and it does work
' Worksheets("sheet1").Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes


' Paul-1 -- this one doesn't work - Err5 Invalid procedure call of argument
' Worksheets("sheet1").Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=aiColNames, Header:=xlYes

' Ken-1 -- this one doesn't work - Err5 Invalid procedure call of argument
' Worksheets("sheet1").Cells(1, 1).CurrentRegion.RemoveDuplicates _
Columns:=WorksheetFunction.Transpose(aiColNames), _
Header:=xlYes

' Ken-2 -- this one doesn't work - Err9 subscript out of range
' Worksheets("sheet1").Cells(1, 1).CurrentRegion.RemoveDuplicates _
Columns:=WorksheetFunction.Transpose(WorksheetFunction.Transpose(aiColNames )), _
Header:=xlYes

'Paul-2 -- this one does work
Worksheets("sheet1").Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=(aiColNames), Header:=xlYes

End Sub

[/VBA]

Paul

[kkb100]

Public Sub remdup2(myArray As Variant, myrange As String)
'
' Passing array with columns to removeduplicates member
' trick is myArray is a variant type and use "Evaluate" to force Columns array to be evaluated.
'
Dim i As Integer
ActiveSheet.Range(myrange).RemoveDuplicates Columns:=Evaluate(myArray), Header:=xlNo
End Sub

[Paul_Hossler]

Interesting

I'll have to try Evaluate method when I get to the Office

Paul

[snb]

Sub ListRemoveDups1()
    Dim sq(2)
    sq(0) = 1
    sq(1) = 2
    sq(2) = 3
    
    x1 = TypeName(sq)
    x1a = VarType(sq)

    x2 = TypeName(Split("1_2_3", "_"))
    x2a = VarType(Split("1_2_3", "_"))

    x3 = TypeName(Evaluate(Split("1_2_3", "_")))
    x3a = VarType(Evaluate(Split("1_2_3", "_")))

    Worksheets("sheet2").Cells(1, 1).CurrentRegion.RemoveDuplicates Evaluate(Split("1_2_3", "_")), xlYes
    Worksheets("sheet2").Cells(1, 1).CurrentRegion.RemoveDuplicates Evaluate(sq), xlYes
    Worksheets("sheet2").Cells(1, 1).CurrentRegion.RemoveDuplicates (sq), xlYes
    Worksheets("sheet2").Cells(1, 1).CurrentRegion.RemoveDuplicates Array(1, 2, 3), xlYes
End Sub