CODEWITHSUNDEEP
vbaexcel
Tan Stanley
Tan Stanley

Reputation: 101

Remove duplicate rows based on dynamic amount of columns

I got the total number of columns, and tried to append it to string in such a way it is 1,2,3,4,5 depending on the number of columns. 

Sub DeleteHighlightedRecords()

 numofCol = ""

     For i = 1 To lcol

        numofCol = numofCol & "," & i

     Next i

 numofCol = Mid(numofCol, 2)

ThisWorkbook.Worksheets(1).Cells.RemoveDuplicates Columns:=Array(numofCol), Header:=xlNo
End Sub

The above code i have will match the combination i placed in Array(), and then remove the duplicates ya? Correct me if i am wrong, however I am getting application-defined error.

How may i set the dynamic numbers of columns into the Array()?

Upvotes: 0

Views: 1660

Answers (2)

Yvan
Yvan

Reputation: 1

I've written a VBA macro that removes duplicates from a dynamic range of rows and columns using CurrentRegion. Here's the working code:

Sub RemoveDuplicates()

    Dim rng As Range
    Dim varArray() As Variant
    Dim index As Long

    ' Set the range to the current region starting from A2
    Set rng = ActiveSheet.Range("A2").CurrentRegion
        
    ' Initialize the array to hold the column indices
    ReDim varArray(rng.Columns.Count - 1) 

    index = 0

    ' Load column indices into the array
    Do Until index > rng.Columns.Count - 1
        varArray(index) = index + 1
        index = index + 1
    Loop
    
    ' Remove duplicates from the range based on all columns
    rng.RemoveDuplicates Columns:=(varArray), Header:=xlYes

End Sub

Upvotes: 0

arcadeprecinct
arcadeprecinct

Reputation: 3777

Array(1,2,3) is not the same as Array("1,2,3"). The first will produce an 3-element array containing 1,2 and 3, the second will produce an 1-element array containing the string "1,2,3".

To create an actual array we can do the following: 

    Dim numOfCol() As Variant 'normally I would choose Long instead of variant but that gave a type mismatch error
    Dim i As Long   'always use "Option Explicit" at the top of your module!
    ReDim numOfCol(0 To (lcol - 1))  'has to be 0-based for RemoveDuplicates

    For i = 0 To (lcol - 1)
        numOfCol(i) = i + 1
    Next i

Now as if using a Variant array instead of a Long array and using a 0-based array wasn't enough, RemoveDuplicates apparently also can't handle arguments passed as a reference to a variable instead of a value. To pass a variable as a value rather than a reference, encase it in parentheses. This causes the variable to be evaluated and then passed to the function.

ThisWorkbook.Worksheets(1).Cells.RemoveDuplicates Columns:=(numOfCol), Header:=xlNo

Upvotes: 5

Related Questions