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 — GSerg
– GSerg, Commented Sep 29, 2018 at 14:58

Doug Glancy · Accepted Answer · 2016-04-23 18:59:09Z

1

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, 2016 at 18:59

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

OldUgly Over a year ago

Very strange. Yes - it needs to be variant, and in the brackets ().

user4831894 Over a year ago

Yep, that was it. I went all around it and just didn't see it. Thanks for your help!

user4831894 Over a year ago

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?

Doug Glancy Over a year ago

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."

user4831894 Over a year ago

Thanks again for all your help

Collectives™ on Stack Overflow

Excel 2013 VBA Range.RemoveDuplicates issue specifying array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related