CODEWITHSUNDEEP
excelvba
Lowpar
Lowpar

Reputation: 907

Function to remove duplicates from entire sheet vba

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

Upvotes: 1

Views: 1939

Answers (2)

GERMAN RODRIGUEZ
GERMAN RODRIGUEZ

Reputation: 515

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

Upvotes: 0

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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.

Upvotes: 1

Related Questions