When working with large datasets, there’s a good chance that one day you’ll need to find unique values, especially unique strings. It might not matter how many times a value or string repeats, only that it exists in the dataset. When you have 50,000 records with potentially hundreds of unique strings, data cleansing becomes easier when these unique records are collected in one place.
.AdvancedFilter method is a very powerful tool for this. The method can retain old data, take worksheet-based criteria, and, most importantly for this tutorial, find unique values. The criteria portion itself is a powerful feature, but we’ll dive into that in another tutorial. Today, we’re going to show you how to collect unique values in one place.
- Setting the Range
- Output in the Same Place or a New One?
- Finding Unique Values
- Is the Original Unique? Full Code
Before we show you fully functional code, we’re going to step through the parameters of
AdvancedFilter so you can make sure you’re using it correctly.
Setting the Range
The AdvancedFilter method operates on a Range object, just as any filter would. Set the range normally, but note that VBA always treats the first row as a row containing headers. If your data has no headers - that is, your first cell is a regular value - the first value may appear twice in the uniques list. This is easy to recognize and adjust, but it is a bit inconvenient if you’re not expecting it. Unfortunately, Microsoft does not give us a
[SourceHasHeaders] type optional parameter here.
Often we want to find uniques in just one column. If the values we want to filter are in Column C, then we’d operate on C like so
Range("C:C").AdvancedFilter 'Option 1 - using a general Range object
Columns(3).AdvancedFilter 'Option 2 - using a column Range object
This is just the non-functional base code - we’ll show you how to get it working properly soon. Also, notice the range can be a single Column from the Columns collection, or it can be a range object.
.AdvancedFilter can operate on more than one column and its row range can be limited, as well, if you only want to filter a subset of the data.
Unique Values Across Multiple Columns
Filtering for unique values behaves properly across columns, too. For example, if Column A contains first names and Column B contains last names, we could use
.AdvancedFilter to find unique First Name + Last Name combinations. This can be extended to any number of columns. Just use
Range("A:B").AdvancedFilter to expand the filter target to both columns.
Output in the Same Place or a New One?
AdvancedFilter can either filter in place, which hides any records that do not match the criteria, or it can output the results to a new location. While filtering in place has its uses, I recommend copying to a new location whenever you have columns on which you do not filter. This is good way to preserve your original data integrity.
Action parameter tells
- output results in the same place as the original (
- copy results to a new location (
xlFilterInPlace! This will filter the entire sheet and hide rows that do not match your filtering condition on your specified column. This can cause users to panic because they don’t realize the data still exists. All you need to do to unhide the filtered rows is select the “Clear” button on the Excel Data tab to reshow the hidden rows. Alternatively, you can run this code to do it programmatically:
If you choose
xlFilterCopy as your
Action parameter, you need to specify where you want to place the copy. The
CopyToRange parameter lets you do this. All you need to do is specify a single cell or you can specify an entire column. If the output range is too small to contain all the results, VBA just overflows the area. This means you can’t limit the output, so choose a column without values or with values you don’t mind overwriting.
There’s another quirk of
xlFilterCopy to be aware of, too. If two columns in the data you’re trying to filter have the same header title, the
xlFilterCopy feature may copy the first column with that name twice to your destination column (
Finding Unique Values
The last piece of the puzzle is the Boolean parameter
Unique, which takes only TRUE or FALSE. To find unique values, set this to TRUE.
To find each unique
Range("C:C").AdvancedFilter xlFilterCopy, , Range("H1:H1"), True
Don’t miss the blank
CriteriaRange parameter. This is our output:
Original Data in Column A and the Filtered Data in Column H
Once you have the list of unique cities, you can further process your data if you’d like using VBA AutoFilter to alphabetize or filter further.
Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
To find unique given-family name combinations, you could use
Range("A:B").AdvancedFilter xlFilterCopy, , Range("H1:H1"), True
Since our list is very short, there are no first-last name combination duplicates and the output matches the input. On the other hand, if we had 3 Samuel Adams, one each in Annapolis, New York, and Erie, we would only see a single instance in the output. Of course, we could also filter on
Is the Original Unique? Full Code
You can check if your original data has any duplicates by counting the input and output from the AdvancedFilter method. If the number of values match, then your original data didn’t have any duplicates to begin with. One way to do this is to use the
WorksheetFunction.Count method. The
xlFilterInPlace approach can also apply here, but keep in mind the warning we talked about earlier.
This fully functional code block tells you whether the data in Column A is unique already or not:
Sub wasOriginalUnique() Dim beforeCount, afterCount As Integer Range("A:A").AdvancedFilter xlFilterCopy, , Range("B:B"), True beforeCount = WorksheetFunction.CountA(Range("A:A")) afterCount = WorksheetFunction.CountA(Range("B:B")) If beforeCount = afterCount Then MsgBox ("The original was unique") If beforeCount <> afterCount Then MsgBox ("The original had repeated records") End Sub
To reinforce the value of the VBA
AdvancedFilter method, let’s so you have values in Column A and you want to copy only unique values to a new column, column H. It only takes one line of code to do this:
Sub uniquesToColumnH() Range("A:A").AdvancedFilter xlFilterCopy, , Range("H:H"), True End Sub
All your unique values from column A now appear in column H. It’s as simple as that. Advanced filtering unique values provides a valuable alternative to the VBA RemoveDuplicates method we wrote about not too long ago.
We’ve now shown you how to filter out unique records, both in single columns or for consecutive columns. You also know how to put the results in a separate space for later comparison and to keep all the data on one page (without hiding large portions of your original dataset).
Once you have the unique records, you can alphabetize and filter further using AutoFilter, both as an entire table or as a single column.
If you found this helpful, please subscribe using the form below and we’ll send you a couple more tips to make sure you’re getting the most out of VBA.