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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.