Function to remove duplicates from entire sheet vba

Question

I cannot get the function below to work. Ideally I would like to create a function that will automatically identify how many used columns are in the sheet then pass that as an array to the delete duplicates. I cannot understand why it is not working. I can confirm that these sheets have headers and multiple rows. I keep on getting error 5 when I try to remove duplicates using the array.

        ReDim colary(1 To wbDestination.Sheets(1).Range("XX1").End(xlToLeft).Column)
        For i = 1 To UBound(colary)
            colary(i) = i
        Next i

        wbDestination.Sheets(1).Range("$A$1:" & wbDestination.Sheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address).RemoveDuplicates Columns:=Evaluate(colary), Header:=xlYes

I found a solution here at the end answered by Osuchov: https://stackoverflow.com/questions/28834596/assign-array-to-removeduplicates-columns — Lowpar, Jan 03 '18 at 22:50

K.Dᴀᴠɪs · Accepted Answer · 2018-01-03T23:52:17.117

Since you were wanting to create a function, here is a method

Sub RemoveDupes(ByVal entireRng As Range, ByVal col As Integer, Optional ByVal containsHeaders As Boolean = True)
    Dim retVal As Integer
    If containsHeaders = True Then
        retVal = xlYes
    Else
        retVal = xlNo
    End If
    entireRng.RemoveDuplicates Columns:=col, Header:=retVal
End Sub

The routine isn't really less work than just typing it out, but you can use it however you desire.

If you want to check multiple columns, then for the col variable you can pass an array.

score 0 · Answer 2 · answered Jan 03 '21 at 20:31

This function remove duplicates from all columns in current worksheet

Sub RemDupCol() 'remove duplicates from all columns
Dim rng As Range
Dim cols As Variant
Dim i As Integer

Set rng = [A1].CurrentRegion 'range bounded by any combination of blank rows and blank columns
ReDim cols(0 To rng.Columns.Count - 1) 'resize a dynamic array already declared
For i = 0 To UBound(cols) 'Loop for all columns
cols(i) = i + 1
Next i
rng.RemoveDuplicates Columns:=(cols), Header:=xlYes 'Removes duplicate values from a range of values.
'Array of indexes of the columns that contain the duplicate information. first row contains header information.
End Sub

Function to remove duplicates from entire sheet vba

2 Answers2