Hello, Excellers welcome back to my blog for another #macromondays #exceltips blog post in my 2019 Monday Series. Today lets look at creating a list of unique values. We will make this lightning fast as we can write some super simple Excel VBA code to remove duplicate values.
Before we get started on our coding, let’s take a look at the overriding syntax which is involved in this process.
The Syntax Of Removing Duplicates With VBA
Range.RemoveDuplicates(Column, Header)
Where
Range – this is the given range specified by the user
Column– this defines the array of indexes of columns for checking the duplicate values. For e.g, if you gave the column position as 1,2,3 then it checks the duplicates only in column 1,2 and 3 by taking the first column in the selected range as the reference.
Header– 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.
Now we know the syntax and what is expected by Excel, let’s get some coding done.
Preparing To Write The Macro
First, we 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. We then have a choice. Either create a module to store your code either in your Personal Macro Workbook or alternatively in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. So, if you store it in the current workbook then use is restricted to that 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
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Writing The Macro
Today let’s take a look at how to remove duplicates from a single column of data.
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 the macro. In this example, I have called the macro RemoveDupes. Excel automatically inserts the End Sub piece of code for us which ends the Sub Routine. So, all we need to do is insert the rest of the code.
So, in this first example, we are going to remove the duplicate year values from the data in Column A. The range of data is A1:A10. My data does contain a header called ‘Years’.
This VBA macro will check only the occurrence of duplicate values in the given range and it doesn’t affect the values outside the specified range. The range specified is the Active Workbook, Sheet 1 and cells A1:A10.
[stextbox id=’info’]Sub RemoveDupes
Set rng = ThisWorkbook.Sheets(1).Range(“A1:A10”)
rng.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub[/stextbox]
The Code contains Array(1) which means that it looks for duplicated years only in Column A. My Header value is xlYes so will automatically ignore the first row for me. That’s it how to write a macro to remove duplicate values.
That is super easy, isn’t it?. If you need to remove duplicates and generate a unique set of values then it is really worth writing this code.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
