VBA: Making a Column Number Array for .RemoveDuplicates

[Jerry Sullivan]

Hi all,

I'm stumped on this one and haven't been able to find the answer through search.

This comes from the macro-recorder when I remove duplicates from the last 3 columns of my range.

Sub Macro1()
' Macro1 Macro
    Range("A1:E8").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$E$8").RemoveDuplicates _
        Columns:=Array(3, 4, 5), Header:=xlYes
End Sub

I want to make a macro that does this using variables instead of Array(3, 4, 5)
but I get an error when trying to pass an array built from variables.

Sub MyTry1()
    Dim iArray() As Integer, i As Integer
    With ActiveSheet.Range("$A$1:$E$8")
        ReDim iArray(1 To .Columns.Count - 2)
        For i = 1 To 3
            iArray(i) = i + 2
        Next i  'Result is iArray= (3, 4, 5)      
        .RemoveDuplicates Columns:=iArray, Header:=xlYes
       'returns Run-time error "5": Invalid procedure call or argument
    End With
End Sub

I've tried Integer, Long and Variant data types, but no luck.

Thanks in advance.

 

[AlphaFrog]

I found this thread that may help...
http://social.msdn.microsoft.com/Forums/ar/isvvba/thread/8083f676-d519-4064-a6b6-0680856fd3eb

 

[Jerry Sullivan]

AlphaFrog,

Thanks so much for tracking down that link.

The suggestion that worked for me was to use a zero-based Variant Array of Integers and wrap the Array Reference in parentheses as shown below.

Sub MyTry2()
    Dim iArray As Variant, i As Integer
    Dim rData As Range
    Set rData = Range("$A$1:$E$8")
    With rData
        ReDim iArray(0 To .Columns.Count - 3)
        For i = 0 To UBound(iArray)
            iArray(i) = i + 3
        Next i
        .RemoveDuplicates Columns:=(iArray), Header:=xlYes
    End With
End Sub

Thanks again- I would have never solved this by trial and error.

 

[AlphaFrog]

You're welcome and thanks for the feedback. The thread listed 2-3 possible solutions and I was curious as to with one actually did the trick.

Are the parenthesis really necessary? I don't doubt you. I don't have Excel 2007 and can't check. It seems strange. I don't get what the parenthesis are doing.

 

[Jerry Sullivan]

You're welcome and thanks for the feedback. The thread listed 2-3 possible solutions and I was curious as to with one actually did the trick.

The Evaluate(iArray) option seems unnecessary since (iArray) works.

Using a string: sArray = "Array(3, 4, 5)" failed - but that suggestion sounded like more of a guess.

I wasn't able to get the "use Object data type" suggestion to work- but that might be my inexperience with that type.

I'm glad to learn this because I've wanted to use variables for SubTotal Column Arrays and hit the same wall. I'm optimistic the same technique will work for SubTotals.

Thanks!

 

[Jerry Sullivan]

You're welcome and thanks for the feedback. The thread listed 2-3 possible solutions and I was curious as to with one actually did the trick.

Are the parenthesis really necessary? I don't doubt you. I don't have Excel 2007 and can't check. It seems strange. I don't get what the parenthesis are doing.

Apparently they are. The last code I posted gives a run-time error without the parenthesis
Contributor Peter Jamieson offered a possible explanation...

"so it presumably has something to do with VBA behaving differently depending on whether a Variant array parameter is passed ByVal or ByRef, but I can't tell you more than that. "

 

[AlphaFrog]

Contributor Peter Jamieson offered a possible explanation...

"so it presumably has something to do with VBA behaving differently depending on whether a Variant array parameter is passed ByVal or ByRef, but I can't tell you more than that. "

Ahhh! Thanks. That makes sense.

 

[xmasenator]

Hi, all.
Same problem. Tried to solve like Jerry did. Now i get a "Type Mismatch" error!!!

How do i get out of this one?
Thanks in advance.

 

[xmasenator]

Solved my own problem using hints from the link that AlphaFrog supplied.
Instead of
.RemoveDuplicates Columns:=(iArray), Header:=xlYeswhat worked for me was:
.RemoveDuplicates Columns:=Evaluate(iArray), Header:=xlYes

 

[Rich Cohen]

I just want to add my thanks for solving a difficult problem. The "Columns:=Evaluate(iArray)" approach worked for me.