While working with huge data, duplicate values are always a concern.
Most of the time I use remove duplicate option to remove all those values.
And, Iβm sure you do the same.
But removing these duplicate values or just counting them never sort out the problem.
The important thing is to review all the duplicate values before deletion.
Yes, thatβs right.
Once you highlight all those values, you can check and then you can delete them.
Today, in this post, Iβd like to share with you 4 different VBA codes to highlight duplicate values.
And the part is, these codes can highlight cell using different ways. You can simply copy-paste them in your VBA editor and use them.
So letβs get startedβ¦
1. Within Each Row
This VBA code checks all the cells from a row and highlights all the cells which are duplicate within a row.
In simple words, if a row has the value β522β twice then it will be considered as duplicate. But if the another 522 is in another row then it will be considered as unique.
Sub DuplicateValuesFromRow() 'Declare All Variables. Dim myCell As Range Dim myRow As Integer Dim myRange As Range Dim myCol As Integer Dim i As Integer 'Count Number of Rows and Columns myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count 'Loop Each Row To Check Duplicate Values and Highlight cells. For i = 2 To myRow Set myRange = Range(Cells(i, 2), Cells(i, myCol)) For Each myCell In myRange If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then myCell.Interior.ColorIndex = 3 End If Next Next End Sub
If you go through this macro, you will find that we have used a loop to check each row for duplicate values and highlight them with a color.
Important Points
- Your data should not have a blank row or column in it, otherwise, it ignores that cell.
- Starting cell of your data should be βA1β cell. And if you want to adjust the starting point you have to adjust the code.
- First row and column of your data sheet should be heading.
2. Within Each Column
This VBA code checks all the cells from a column and highlights all the cells which are duplicate within each column.
In simple words, if a column has the value β231β twice then it will be considered as duplicate. But if the another β231β is in another column then it will be considered as unique.
Sub DuplicateValuesFromColumns() 'Declare All Variables Dim myCell As Range Dim myRow As Integer Dim myRange As Range Dim myCol As Integer Dim i As Integer 'Count number of rows & column myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count 'Loop each column to check duplicate values & highlight them. For i = 2 To myRow Set myRange = Range(Cells(2, i), Cells(myRow, i)) For Each myCell In myRange If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then myCell.Interior.ColorIndex = 3 End If Next Next End Sub
3. Within Selection
Letβs say you just want to highlight cells with duplicate values from the selection, this code can help you in this.
To use this code you just need to select a range of cells and run this code. It checks each cell with the selection and highlights it with red color if a cell has a duplicate value.
Sub DuplicateValuesFromSelection() Dim myRange As Range Dim i As Integer Dim j As Integer Dim myCell As Range Set myRange = Selection For Each myCell In myRange If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then myCell.Interior.ColorIndex = 3 End If Next End Sub
4. Entire Data
If you have a large data-set, you can use this macro code to check the entire data and highlight duplicate values.
This code loops through each cell one by one and applies red color all those cells which are the duplicate.
Sub DuplicateValuesFromTable() Dim myRange As Range Dim i As Integer Dim j As Integer Dim myCell As Range Set myRange = Range("Table1") For Each myCell In myRange If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then myCell.Interior.ColorIndex = 3 End If Next End Sub
Important Note: In the above code, we have used the table name "Table1", you can change this name from the code. Or, if you have a range then you can simply use range name.
[Bonus Tip] Count Duplicate Values
This code helps you to count the numbers of duplicate values from the selection. When you run this code, it returns a message box which shows the count.
Sub CountDuplicates() Dim i As Integer Dim j As Integer Dim myCell As Range Dim myRange As Integer myRange = Range("Table1").Count j = 0 For Each myCell In Range("Table1") If WorksheetFunction.CountIf(Range("Table1"), myCell.Value) > 1 Then j = j + 1 End If Next MsgBox j End Sub
Again in the above code, we have used table name and you change it or replace it with a range.
Sample File
Download this sample file from here to learn more.
Conclusion
In different situations, we need check duplicate values in the different ways. And, all the above codes can be helpful for you in this.
If you want you can change the highlight color from the codes using color index number.
Now help me on one thing.
Where can we improve these codes?
Please share your views with me in the comment section, Iβd love to hear from you. And, donβt forget to share this tip with your friends.
Must Read Excel Tutorials
VBA is one of the Advanced Excel Skills, and if you are getting started with VBA, make sure to check out there (What is VBA, Excel Programming, and Useful Macro Examples and VBA Codes).
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ο¬nd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
Your VBA Macro Codes are very helpful. Thanks a lot.
In this case, how can i break the tables into some dynamic 5X5 arrays and get the only duplicate values inside a small particular array?? with count of the duplicate numbers inside the array?
Hey Puneet, This is great , I have gone through the codes & it’s working Fine.. can you tell me how to record & concatenate all those duplicate values in one cell?
Hi puneet,
I want to make a macro where I have 50000 rows in a report having duplicates lines more than 1 in a given column.
2 major things needed in macro :
1) have to randomly select only 1000 lines not more or less than that
2) in those randomly selected 1000 lines I want to have all duplicates of a specified value to be there, like if I select 900 and left with 100 more to select and my random selection pick a number which has 120 duplicates value to be part of those 100 lines remaining, then all my duplicates won’t be covered in those 1000 as out of 120 duplicates only 100 are selected and 20 remains there in sheet and not part of that random 1000 selection.
After this want to delete remaining 49000 lines and only want selected 1000 lines as my new data.
Hi, I need to find out duplicate of some specific data from a range. such as from these following values, I need to know 350 and 250 are duplicate or not. As I am new learner of Vba, pls help me. 250,350,400,425,400,325,350
I learn a lots of things here. It is an amazing post , thanks for help me.
Your code may be a bit old. It does not appear to work on Excel Office 365. I had to locate code from elsewhere.
I was looking for good ‘looping though a column for duplications’ routine (your section 2 above) which is very good and just what I was looking for however, there is a slight mistake as you have a variable ‘myCol’ to count the number of columns but you didn’t use the variable to create a loop such that the routine will continue to check for duplicates how ever many columns there are in your worksheet instead of stopping once you reach the last column of your data.
Useful bit of code though π
thanks Mr Puneet
in one condition you mentioned that column and row should be headings.
it’s ok with column but what if my row doesn’t have headings, consider the row starts from a1
Master class
Good blog, where did you come up with the knowledge in this piece of content? Iβm glad I found it though, ill be checking back soon to see what other articles you have.
MS Excel
Hi, Thank you for the good work.
There is a typo in the link “What’s next in BBA?”.
It should read: https://excelchamps.com/blog/vba-to-create-pivot-table/
Thanks Serge.
Hi puneet could you provide formula for compare duplicates by rows in multiple columns?
Hello Ratanak, You can use Conditional Formatting for.
https://www.excelchamps.com/blog/2016/01/use-formulas-in-conditional-formatting/
Great work.please highlight each set of duplicate values by different colors it will be more cool
Thank you Puneet for this
You are welcome Inet.
Good workPuneet-Gogia.It would be great if each set of duplicate values were uniquely highlighted – perhaps in different colours.
Hey Farhad,
Thanks for your words & suggestion.
we can do this by creating a loop in color. Let me update this.
Love PG