Excel 2013 VBA Range.RemoveDuplicates issue specifying array

Question

The sheets that I am scanning for duplicates have different numbers of columns

I'm trying to specify the array of columns for Range.RemoveDuplicates by using a string like this:

Let's say there are 5 columns in this sheet

Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim strColumnArray As String

    With ActiveSheet

        lColumn = Cells(1, Columns.Count).End(xlToLeft).Column

        strColumnArray = "1"
        For i = 2 To lColumn

            strColumnArray = strColumnArray & ", " & i

        Next i

    'String ends up as "1, 2, 3, 4, 5"

        Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
        Rng.RemoveDuplicates Columns:=Array(strColumnArray), Header:=xlNo

    End With

And I get the Run-Time error 13 Type Mismatch" error

So I read posts and saw where someone did it by specifying it as an array like this:

Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim strColumnArray() As String

    With ActiveSheet

        lColumn = Cells(1, Columns.Count).End(xlToLeft).Column

        ReDim strColumnArray(lColumn) As String

        For i = 1 To lColumn + 1    'The array needs to start with 1

            strColumnArray(i) = i

        Next i

        Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
        Rng.RemoveDuplicates Columns:=strColumnArray, Header:=xlNo

    End With

I've tried it this way:

Rng.RemoveDuplicates(strColumnArray, Header:=xlNo)

amd this way:

Rng.RemoveDuplicates(Columns:=Array(strColumnArray), Header:=xlNo)

and in endless combinations of Variant, String, Integer, etc.

I must just be missing it at this point

any help would be appreciated

Possible duplicate of [VBA: Remove duplicates fails when columns array is passed using a variable](https://stackoverflow.com/questions/39104653/vba-remove-duplicates-fails-when-columns-array-is-passed-using-a-variable) — GSerg, Sep 29 '18 at 14:58

score 3 · Accepted Answer · answered Apr 23 '16 at 18:59

3

I had to go back and re-read my post on this. I think the array has to be a Variant. Anyways, this seems to work:

Sub RemoveDupes()
Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim ColumnArray As Variant

    With ActiveSheet
        lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
        ReDim ColumnArray(lColumn - 1)
        For i = 0 To lColumn - 1  'The array needs to start with 1
            ColumnArray(i) = i + 1
        Next i
        Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
        Rng.RemoveDuplicates Columns:=(ColumnArray), Header:=xlYes
    End With
End Sub

answered Apr 23 '16 at 18:59

Doug Glancy

27,214
6
67
115

Very strange. Yes - it needs to be variant, and in the brackets `()`. – OldUgly Apr 23 '16 at 20:23
Yep, that was it. I went all around it and just didn't see it. Thanks for your help! – mreinsmith Apr 23 '16 at 22:40
Just a curiosity, you changed the array loop to start at zero while of course still starting the numbering with 1. Is it possible to skip the first position or will that cause an error in the array? – mreinsmith Apr 23 '16 at 22:47
My post that I linked to in the answer says that the array must be zero-based. I'm not sure where I got that from - it's not in the couple of MSDN pages I just looked at - but simple testing seems to bear it out. If the code above is changed to `Redim` from 1 to 3 it errors with "Runtime Error 5: Invalid Procedure Call or Argument." – Doug Glancy Apr 24 '16 at 14:47
Thanks again for all your help – mreinsmith Apr 26 '16 at 17:13

Excel 2013 VBA Range.RemoveDuplicates issue specifying array

1 Answers1

Linked