Hello Excellers and welcome back to another blogpost of my #MacroMonday #Exceltip 2019 series. Today I want to share with you how to highlight duplicate values in your Excel data set. This small piece of VBA macro code will let you select a range of cells, and find the duplicates within the cell range with the click of the mouse.
Preparing To Write The Code.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save you code in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
As you can see this code will be useful to reuse in any workbook. I will create and save this for future use in my Personal Macro Workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro.
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply HighlightDupes. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
[stextbox id=’info’]
Sub Highlight Dupes
End Sub
[/stextbox]
Declaring Variables.
We need to declare a few variables in this macro. This simply means Excel creates a memory container for the values. In this case, it is the selected range of cells. We also use the third line of code Set myRnge = Selection, to set the range to be stored and analyzed by Excel for any duplicate values.
[stextbox id=’info’]
Dim myRnge as Range
Dim myCell as Range
Set myRnge = Selection
[/stextbox]
Look For Duplicate Cell Values.
This is the meat of the VBA code. We use the For Each loop to loop through all of the user-selected cells and count how many time the value of each cell occurs. We use the COUNTIF Function as we could in Excel normally to calculate how many time as cells value is present in the selection. If it is greater than 1 (or duplicated) then I have chosen to color those cells green.
[stextbox id=’info’]
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 4
End If
Nex
[/stextbox]
Ending The Excel Macro.
Excel carries on looping through all cells in the range until all have been counted. Once all of the cells have been looped then the code finishes. We already have the End Sub piece of code which was already inserted automatically when we started the Macro.
[stextbox id=’info’]
End Sub
[/stextbox]
So, that is how to write a simple VBA Macroto highlight duplicate values in a user selected range of cells.
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
