RemoveDuplicates retrieves Error 5: Invalid Procedure Call or Argument' when using variable on 'Columns' parameter

Question

I need to 'RemoveDuplicates' on a specific 'Selection' which could have anytime any rows/columns size. I am using a Variant type variable for the 'Columns' parameter but it's retrieving an 'Error 5'. What is wrong with my code?

The code shared below shows how the macro copies data from already-known CSV file full paths 'RutaArchivo(i)' and paste them all in an unique workbook 'LibroTemporal(0)', one below another. As a maybe-relevant info I needed to insert a 'TextToColumns' procedure each pasting process, so I could be able to keep updating 'Ancho' with how many columns unique data is compiling.

'MiArray()' and 'MiArray2()' are the Variant type variables used to replace manual arrays in my procedures: 'MiArray(1 To Ancho)' is used on 'TextToColumns' procedure (and it works perfect!), now, 'MiArray2(1 To Ancho)' is used on 'RemoveDuplicates' procedure (it doesn't work... Help!).

Notice that I've omitted some other sections of the code to avoid irrelevant info, but this part takes pretty much of what you might need to find the issue. However feel free to ask if you need more info.

I know this question has been made by many people, but for some reason it's not working on my case, and I need some customized support. I've done some research here and in other websites and most of them (which get success) ends with some similar solution as the one I'm using (adjusted to my case), but I'm having no success.

Dim LibroTemporal(1) As Workbook
Dim i As Integer
Dim j As Integer
Dim Ancho As Integer
Dim MiArray() As Variant
Dim MiArray2() As Variant

    For i = 1 To UBound(RutaArchivo)
        Workbooks.Open RutaArchivo(i)
        Set LibroTemporal(1) = ActiveWorkbook
        Cells(1, 1).Activate
        If Cells(2, 1) <> "" Then
            If Cells(1, 2) = "" Then
                Range(ActiveCell, ActiveCell.Offset(ActiveCell.End(xlDown).Row - 1, 0)).Select
            Else
                Range(ActiveCell, ActiveCell.Offset(ActiveCell.End(xlDown).Row - 1, ActiveCell.End(xlToRight).Column - 1)).Select
            End If
            Selection.Copy
            LibroTemporal(0).Activate
            ActiveCell.PasteSpecial (xlPasteValues)
            Ancho = Application.WorksheetFunction.Max(Selection.Columns.Count, 1 + Len(ActiveCell) - Len(Replace(ActiveCell, Chr(9), "")), Ancho)
            If Selection.Columns.Count = 1 Then
                ReDim MiArray(1 To Ancho)
                ReDim MiArray2(1 To Ancho)
                For j = 1 To Ancho
                    MiArray(j) = Array(j, 1)
                    MiArray2(j) = j
                Next j
                Selection.TextToColumns Destination:=Cells(1, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                    :=(MiArray), TrailingMinusNumbers:=True
            End If
            ActiveCell.Offset(Selection.Rows.Count, 0).Select
        End If
        Application.DisplayAlerts = False
        LibroTemporal(1).Close
        Application.DisplayAlerts = True
    Next i

    Range(ActiveCell, Cells(1, 1).Offset(0, Ancho - 1)).Select
    Selection.RemoveDuplicates Columns:=(MiArray2), Header:=xlNo

I was expecting to have no errors and the duplicates remotion successful, but I get the 'Runtime Error 5: Invalid Procedure Call or Argument'

Thanks in Advance. AJ

Hello Scott! Thanks for asking. Yes, as you can see in the code above, I used them like this (MiArray2) for the RemoveDuplicates. Also I did it for TextToColumns with (MiArray). It's very confusing how it works for one case but not for the other. :( — AJ Suarez, Aug 13 '19 at 14:17
Make the array bounds start at 0, then wrap it in `CVar()` when using it as the argument. — Rory, Aug 13 '19 at 14:18
@Rory, can you post it as an Answer please? SO I could give you the points. — AJ Suarez, Aug 13 '19 at 15:01
@Rory: Didn't ment to steal you the answer - saw all the comments only after I posted my answer... — FunThomas, Aug 13 '19 at 15:48

score 1 · Accepted Answer · answered Aug 13 '19 at 15:06

I was about to flag this as duplicate but figured out that the current existing answers don't address the fact that there may be two issues when using a variable as column-parameter, both causing a runtime error 5.

See Error on remove duplicates

When using an array-variable, it needs to be 0-based. So, you need to write

ReDim MiArray2(0 To Ancho-1)
For j = 1 To Ancho
    MiArray2(j-1) = j
Next j

(don't get your logic with Myarray, but I thinks that's not the issue here)

VBA: Remove duplicates fails when columns array is passed using a variable

You have to put the array in () (you do already)

An extra remark: You don't need the Select-statement, simply write

Range(ActiveCell, Cells(1, 1).Offset(0, Ancho - 1)).RemoveDuplicates _
    Columns:=(MiArray2), Header:=xlNo

Thank you @FunThomas `MiArray` is same thing than `MiArray2` (a dynamic array, but it's used for `TextToColumns` with a different structure). However, that wasn't giving me any issues. Also, you're right about the `Select` statement, thanks! I just used it to confirm i was selecting the range properly before the duplicates removal! Now, I appreciate you reminded me to remove that statement! lol — AJ Suarez, Aug 13 '19 at 17:09

RemoveDuplicates retrieves Error 5: Invalid Procedure Call or Argument' when using variable on 'Columns' parameter

1 Answers1