Excel VBA Remove Duplicates

In this video we will explain how you can remove duplicates using Excel VBA. Feel free to copy and use this code as you please. You can find it further down in the description.

0:00 Excel VBA Remove Duplicates Intro 1:04 Developer Tab 2:07 Removing Duplicates in a Range 4:15 Removing Duplicates in a Table Object

Code:

Sub removeDupsRange()

Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range

Set rng1 = Range("A1").CurrentRegion Set rng2 = Range("D1").CurrentRegion Set rng3 = Range("G1", Range("G1").End(xlDown))

rng1.RemoveDuplicates Columns:=1, Header:=xlYes rng2.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes rng3.RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Sub removeDupsTable()

Dim ws As Worksheet Dim tbl As ListObject

Set ws = ThisWorkbook.Worksheets("Sheet1") Set tbl = ws.ListObjects("CountryRegion")

tbl.Range.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

End Sub

Video guidance:

We have already prepared the VBA code called removeDupsRange and removeDupsTable. Remark that we have written this code into a module. To open a new one, you navigate to Insert and select Module. Here you can start typing your code.

Now we are ready to go through the code. First, we see what to do, to remove duplicates in a range. Next, we go to the removeDupsTable to see how you can remove duplicates in a table object.

The first thing we do in the removeDupsRange function is to initialize the variables used. We have range 1 representing the range of country and region starting in cell A1.

The second range represents the range of country and region starting in cell D1. Remark that CurrentRegion returns a range of all the adjacent cells to the given range, so here the entire table.

Finally, range 3 starts in cell G1 and selects all cells below, so here we have the entire “Country” column.

Next in this Excel VBA remove duplicates tutorial, we remove the duplicates in each range. We call the RemoveDuplicates function on our range, we specify in which columns the duplicates should be removed and we have a header in our range, so we set this to xlYes.

To run the code, you navigate to Developer and select Macros. Here, you select the function you want to run, at the moment it is removeDupsRange, and you press Run. In range 1 the duplicate values in the “Country” column are searched for and those duplicate rows are deleted.

In range 2 the unique combinations of “Country” and “Region” are withheld. Finally, in range 3 only duplicate values in the “Country” column are removed without any impact on the “Region” column. This is the case since we only selected the “Country” column as range 3.

The second code we prepared is for the table object. To remove the duplicate values from there we first initialize the ... https://www.youtube.com/watch?v=gmocnhkMMog