Excel VBA remove duplicates method
In this post, you’ll learn different methods of Excel VBA remove duplicates.
If the data in one row find an exact match with any other rows in a given range then it’ll be considered as duplicate and using remove duplicates function you can remove the duplicates and keep unique value.
Table of Contents
The syntax of Remove Duplicates in Excel VBA
Range.RemoveDuplicates(Column, Header)
Range- It defines the given range specified by the user
Column- It defines the array of indexes of columns for checking the duplicate values. For e.g, if you gave the column position as 5,6,7 then it checks the duplicates only in column 5,6 and 7 by taking the first column in the selected range as the reference.
Header- It determines whether the given range does contain a header or not. The user can specify three values in the header parameter. They are shown below
Header:=xlYes- Choose this value if a given range does contain Header
Header:=xlNo – Choose this value if given Range doesn’t contain header
Header:=xlGuess- Choose this value if you want to excel to determine the existence of Header.
VBA remove duplicates in a single column
You can find the Month names listed in column B of “sheet 1” and it does contain header “Month”. Column B contains few month names which are repeated over more than one time. We are now going to use VBA to Remove duplicate values in Column B (Single column) of Range B1: B20. This VBA duplicate function code check only the occurrence of duplicate values in the given range and it doesn’t affect the values outside the specified range.
The above code contains array value as 1 so it finds the duplicates only in the column B and Header value is xlYes so it ignores the first row.
VBA remove duplicates in a given Range
Above data contain Marks of three different subjects scored by 11 Students. We are now going to use VBA to remove duplicate marks. For e.g., if more than one student secured the same marks in Maths, physics, and chemistry then we are going to remove all the duplicates rows. All the colored rows contain duplicate marks and using VBA we are going to remove the multiple occurrences and keep a unique value.
The above code contains array value as 1,2,3 so it finds the duplicates by looking at the values in column A, B, and C of each row and Header value is xlYes so it ignores the first row.
VBA remove duplicates in a given range for specified columns
Above data contain Marks of four different subjects scored by 12 Students. We are now going to use VBA to remove duplicate marks of two subjects Maths and Physics. For e.g., if more than one student secured the same marks in Maths and Physics then we are going to remove the duplicates rows. Some rows contain duplicate marks and using VBA we are going to remove the multiple occurrences and keep a unique value.
The above code contains array value as 1 and 2 so it finds the duplicates by looking at the values in column A and B of each row and Header value is xlYes so it ignores the first row.
VBA to remove duplicates in an unknown range for specified columns
We are going to use the same data but we assume that we don’t know the range. In this case, we have to use UsedRange property to determine the range before proceeding with remove duplicates.
To do that we need to replace Set Rng = ThisWorkbook.Sheets(1).Range(“A1:D13”) with Set Rng = ThisWorkbook.Sheets(1).UsedRange.
VBA to remove duplicates in an unknown range and unknown/dynamic columns
We are going to use the above data to remove duplicates in an unknown range and unknown columns. First of all we need to determine the range and in order to determine that we need last column and row count. Once we found that we can easily convert it as Range. After that we need to store the column count in a dynamic array. Pass this array value in the Column parameter of remove duplicates function.
The above code contains the number of columns as an array so it finds the duplicates by looking at the array values and Header value is xlNo so it considers the first row. You should note that we define the range from Cells(2, 1) or B1.
Recommended Excel VBA books
About The Author
Rajan
Rajan is a web geek-Blogger-Programmer- working in corporate firm as system analyst engineer. Whenever time permits he used to blog on recent trends in technology, monetizing tips, Programming concepts and technical guides to beginners in amarindaz.
AWESOME. Very clear, different options for the same issue and best of all, it works beautifully. I’m new in VBA and I still have a long way to go.
Thank you very much
R
I’m so glad that I could helped you
HI, this is very cool code and very helpful. Honor to you – thanks
I’m so glad that I could helped you
HI RAJAN WELL EXPLAINED ARTICLE THANKS
Thank you Ranish 🙂
Good code! I’ve yet to explore the use of “Array” but I should because it’s super efficient and fast. This was very helpful for me.
Hi
I need help for removing duplicate values from Column B in excel
Could you plz provide me the formula.
Thanks in Advance
Are you looking for Macro or want to do in excel?
Excel :
Select column B Go to Data tab and select remove duplicates-> Continue with current selection-> Click Ok
Macro:
Sub Remove_Duplicates()
‘Give the Range
Set Rng = ThisWorkbook.Sheets(1).Range(“B1:B20”)
Rng.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub
Its just great. I am developing something and i was using long long cods in VBA to remove duplicates from very large data which was almost hanging my system. This code is perfact. even works better in sheet’s selectionchange event….great. appriciate it
Thank you.
Hi rajan,
I used your last code, but get a debug error 5. I have a tabel A:Q rows ? variable….
header on row 2 and would like to check the variables fron C for duplicates, if double, delete 1 row
B variabel not the same in the 2 duplicate rows.. so must be ignored..
variable in cells coloumn c = ex. 08d0f8fe-3306-4071-aef0-8bbac86d8db8
I had the following :
Set Sht3 = Worksheets(“ClosedOrders”)
LastCol = Sht3.UsedRange.Columns.Count
LastRow = Sht3.UsedRange.Rows.Count
Set Rng = Sht3.Range(Cells(3, 3), Cells(LastRow, LastCol))
‘find the number of columns and enter into the array
ReDim varArray(LastCol – 1)
‘ReDim varArray1(Application.WorksheetFunction.CountA(rng))
Index = 0
‘loop through range and load values to the array
Do Until Index > LastCol – 1
varArray(Index) = Index + 1
Index = Index + 1
Loop
‘remove duplicates
Rng.RemoveDuplicates Columns:=(varArray), Header:=xlNo
debug on the last line …
any idea ?
Thanks
chris
Hello, hoy can you verify if actually remove duplicate?
how can i do this
set c= Rng.RemoveDuplicates Columns:=varArray),header:=xlNo
Nope its very simple anyone can start blog and ofcourse no programming needed