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.

The VBA .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.

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

or

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?

VBA 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.

The Action Parameter

The Action parameter tells .AdvancedFilter to

  1. output results in the same place as the original (xlFilterInPlace) or
  2. copy results to a new location (xlFilterCopy)

Beware of 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:

ActiveSheet.ShowAllData

CopyToRange Parameter

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 (CopyToRange).

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 City in the dataset shown below and place the list of unique cities in Column H, we can use this code

Range("C:C").AdvancedFilter xlFilterCopy, , Range("H1:H1"), True

Don’t miss the blank CriteriaRange parameter. This is our output:

VBA AdvancedFilter Unique Values from Column A to H
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 macros with our free VBA Developer Kit

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 Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

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 Family Name + Given Name + City, which would preserve all three in that example.

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.