Excel VBA Remove Duplicates Multiple Columns

This Excel VBA procedure will remove all of the duplicate values in multiple columns.  Since Excel 2010 we have been able to remove the duplicates in a column.  With the assistance of VBA we can automate this task to remove all of the duplicates in all of the columns or you can choose which columns to remove the duplicate values from.

The following is the Excel VBA code which remove all duplicates in all of the columns of a particular sheet.

Option Explicit

Sub DeDupeCols() 'Excel VBA to remove duplicates from all columns
Dim rng As Range
Dim cols As Variant
Dim i As Integer

Set rng=[A1].CurrentRegion
ReDim cols(0 To rng.Columns.Count - 1)
For i=0 To UBound(cols) 'Loop for all columns
cols(i)=i + 1
Next i

rng.RemoveDuplicates Columns:=(cols), Header:=xlYes
End Sub

If you wanted to remove the duplicates in say columns (1, 2, 3) then the following would help.

Sub DeDupeColSpecific() 'Excel VBA to remove duplicates from specific columns
Cells.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub

One thing to point out about the above two procedures if there is a duplicate say the second instance in A3 but column C3 has a unique item then the unique item in column C will be preserved and the line will remain intact leaving a duplicate value in A3.

To remove duplicates treating each row individually a different method is required.

Option Explicit
Sub test() 'Excel VBA to remove duplicates treating each column individually.
Dim ar As Variant Dim i As Integer

ar=[{1,2}]

For i=1 To 2 'Change to suit (1 to 2 represents above array)
Sheet1.Columns(ar(i)).EntireColumn.Copy Sheet2.[A1]
Sheet2.Range("A1:A100").RemoveDuplicates Columns:=ar(1), Header:=xlNo
Sheet2.Columns(1).EntireColumn.Copy Sheet1.Cells(1, ar(i))
Next i
End Sub