PDA

View Full Version : Solved: Excel2007 .RemoveDuplicates



Paul_Hossler
04-14-2011, 06:28 PM
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?



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


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
04-14-2011, 07:07 PM
Well, that was interesting

Thanks to some ideas here ...

http://database.ittoolbox.com/groups/technical-functional/excel-l/excel-2007-removeduplicates-dynamically-generating-the-columns-parameter-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 (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


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


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
04-14-2011, 07:09 PM
When working with arrays named a() for example use:
WorksheetFunction.Transpose(a)
'or
WorksheetFunction.Transpose(WorksheetFunction.Transpose(a))

Paul_Hossler
04-17-2011, 02:40 PM
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



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



Paul

kkb100
11-21-2013, 07:49 PM
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
11-22-2013, 05:35 PM
Interesting

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

Paul

snb
11-23-2013, 05:51 AM
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