none
Remove Duplicates Columns in VBA

    Question

  • In vba, is there a way to assign a variable (variant datatype, array of integer values) to the Columns parameter of the Range.RemoveDuplicates method?  I have searched many forums but have not found a solution that works for Excel 2007.  Direct assignments (e.g. Columns:=arrCols or Columns:=arrCols()) result in an error condition - generally an invalid procedure call or argument.  Using the Evaluate method (Columns:=Evaluate(arrCols)) the procedure runs without generating an error but only the first element of the array is used by the remove duplicates method.
    Wednesday, November 30, 2011 5:44 PM

Answers

  • Sub Macro5()
        Application.CutCopyMode = False
        Dim r As Range
        Dim ary
        ary = Array(1, 2)
        Set r = ActiveSheet.Range("$A$1:$B$8")
        r.RemoveDuplicates Columns:=(ary), Header:=xlYes
    End Sub

     

    Note the parentheses


    gsnu201111
    Wednesday, November 30, 2011 6:03 PM
    Moderator

All replies

  • Sub Macro5()
        Application.CutCopyMode = False
        Dim r As Range
        Dim ary
        ary = Array(1, 2)
        Set r = ActiveSheet.Range("$A$1:$B$8")
        r.RemoveDuplicates Columns:=(ary), Header:=xlYes
    End Sub

     

    Note the parentheses


    gsnu201111
    Wednesday, November 30, 2011 6:03 PM
    Moderator
  • Thank you Gary's Student

    I had a real duhhh of course moment when I saw your answer ---- (*) --- so obvious yet it eluded me.  What's next, breathing?

    db

     

     

    Saturday, January 21, 2012 8:04 PM