While working with huge data, duplicate values are always a concern. Most of the time I use the 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 sorts 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, that these codes can highlight cells using different ways. You can simply copy-paste them into your VBA editor and use them.
1. Within Each Row
This VBA code checks all the cells from a row and highlights all the cells which are duplicated within a row. In simple words, if a row has the value “522” twice then it will be considered a duplicate. But if the another 522 is in another row then it will be considered 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 the “A1” cell. And if you want to adjust the starting point you have to adjust the code.
- The 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 duplicated within each column. In simple words, if a column has the value “231” twice then it will be considered a duplicate. But if 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 the 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 to all those cells which are duplicates.
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 the range name.
[Bonus Tip] Count Duplicate Values
This code helps you to count the number of duplicate values from the selection. When you run this code, it returns a message box that 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 a 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 to check duplicate values in 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 the color index number.
Now help me with 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.
I am so appreciative for this work that you have done. I never thought that I would find this such quality instruction at no cost. This encourages me to purchase a text from you if one becomes available. I do have a couple of comments regarding this section of lessons: From Sub DuplicateValuesFromColumns(), I had to change
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
to
myRow = Range(“B2”).End(xlDown).Row
myCol = Range(“B2”).End(xlToRight).Column
in order to get an accurate count of myRow and myCol
You can share it here, I’ll try to guide you with my best.
Your VBA Macro Codes are very helpful. Thanks a lot.
if cell contains multiple words, so this code works to capture repeated words
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/vba/vba-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://excelchamps.com/conditional-formatting/formulas/
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