Join Curt Frye for an in-depth discussion in this video Finding unique items using xlFilter, part of Excel VBA: Managing Files and Data.
- One of the more interesting ways to analyze your data in Microsoft Excel is to find unique values within a data set. For example, you might look at your orders and see how many different states you have received orders from. In this movie, I will show you how to use VBA to create a filter that displays only unique items within a list. As my sample file, I will use the FindUnique.xlsm sample workbook. That is a macro-enabled workbook that you can find in the Chapter 1 folder of your Exercises Files collection.
This is a very simple workbook. I have just a single list on one worksheet and it's a list of states. And you can see that I have Washington, Oregon, California, and Arizona here on the side and the list is repeated twice. And, again, my goal is to use VBA to filter this list so I only see the first occurrence of each unique value. I'll go ahead and press Alt F11 to switch over to the Visual Basic editor. And you can see here that I have my VBA code, my subroutine called UniqueItems already written.
And the main reason I did that is because creating filters using VBA isn't normally something you do by hand. The best way to do it is to record a macro view applying a filter or creating a filter and then use that macro code in your VBA. And, that's exactly what I've done here with a few modifications. So let's just take a look, briefly, at this code. I have defined a variable called string address and it is a string. And I also either turn on or turn off auto filter errors for wherever they're currently applied.
Next, I assign the current region of the active cell's address to the string address variable. And what that does, because my data, pressing Alt F11 quickly, is in cell A1 to A9, the current region is A1 to A9 and I'll click in cell A5 so that the cell is within the active region. It's very important that you do that for these macros to work. So, I'll press Alt F11 to go back. And, then, the last command, is the one that actually applies the filter. It uses the range of objects, AdvancedFilter method, and we identify the Action as xlFilterInPlace which means that rather than copying the filtered values to another place on the worksheet, it's going to leave them where they were and it sets the Unique parameter to True.
So what this code does, is apply an advanced filter, it filters the value in place, and it finds only the unique values. So what that means, in this context, is that it's looking for the first occurrence of the each value in the list. So, with all that in place and explained, I'll click inside of the code for the UniqueItem subroutine and press F5 to run it. And Alt F11 to switch back to the workbook. And, as predicted, you can see that the states Washington, Oregon, California, and Arizona only occur once.
And, you'll also notice that I have no filter errors. And that's because I turned auto filtering off. But I can go over on the Home tab of the ribbon to the Sort & Filter button and click Filter. Doing so removes the filter from the worksheet. Now one thing that I want to point out, is that finding unique values inside a of list takes a look at the entire row. So what that means, in this context, is that I kept my list deliberately simple. There's only a single column of States.
If I were to add a second column, let's call it Sales, and I'll apply my filtering, just to make sure that it stands out as a column. And I have sales of one, two, three, four, five, six, seven, and eight. Just so they're different numbers in each row. Then the filter that I created will operate on each of these rows as a separate unique item. So, I'll click in cell B5 and I'll Alt F11 to switch back.
Actually, I'll make sure that I've selected a state just to prove my point. So what that means, in this context, is that the active cell, even thought it's in the column for States, the filter that we're creating takes a look at the entire row. So Washington with one sale and Washington with fives sales are different even though the active cell is in the State column. So, I'll press Alt F11 to switch back to the Visual Basic editor. press F5 to run the code, Alt F11 to switch back, and you can see that even though the filter's been applied as it was before, all of the rows are all there.
Finding unique items is an extremely useful capability to have in Excel. But always remember that finding unique items operates on the entire row in a data list. If there's any difference between any item in any column within that list, then you'll see the row after you apply the filter.
- Creating filters
- Chaining and combining filter criteria
- Determining whether workbooks and worksheets exist, with VBA
- Opening, closing, and saving workbooks
- Using VBA to calculate data via Excel's built-in functions
- Creating charts with sparklines
- Adding fields to UserForms