From the course: Excel VBA: Managing Files and Data

Create a filter - Microsoft Excel Tutorial

From the course: Excel VBA: Managing Files and Data

Start my 1-month free trial

Create a filter

“

- [Instructor] It's easy to store, summarize, and visualize a lot of data in an Excel worksheet. The problem in fact can be limiting the data so that you get the information you need to make a decision. In this movie, I will show you how to use VBA to implement a simple filter in Excel. My sample file is 01_02_CreateFilter, and you can find it in the chapter one folder of the exercise files collection. I have set up this worksheet in a somewhat unusual way. You see that I have two data areas. The first has sales data for the year 2019, and the second below it has data for the year 2020. And I did that so I can demonstrate some of the ways that VBA interacts with filters in your worksheet. Because if you don't pay attention to these details, your filters may act in unexpected ways. So, that's the data that we have. Now, press alt, F11 to move to the visual basic editor. In the visual basic editor, I have a code module and here I have string address as a string and then a line I've commented out that I will describe later, and below that I have a bit of code that assigns the active cells current region to a variable called string address, and below that I have a filter, or auto filter command. Right now, I have an address hard-coded in, that is range A1 to C8, and that is the upper range in the worksheet that I showed you earlier. The filter, which uses the auto filter method, operates on field number two, which is the second field, counting from the left, field is another word for column, and the criteria, criteria one is for Idaho. So, if I run this code by clicking inside of it and pressing F5, and then alt, F11 to switch to the worksheet, you'll see that the top area has been filtered, so we only see the results for the state of Idaho. If I want to clear the filter, I can click the down arrow or the filter arrow, and then click clear filter from state, and then I'll remove the filter arrows by going to the home tab, clicking sort and filter, and then clicking a filter to remove the arrows. And it's important that you remove the arrows to ensure that you are operating on the correct area of your worksheet and I'll show you what I mean. First, I will click a cell in the bottom data area, the values for 2020, and then press alt, F11 and I will edit my code so that instead of filtering the region A1 to C8 I will filter based on the address, in the string address variable. So, I'll copy a string address and paste it over the range, A1 to C8 that I had. And now when I run the filter wherever the current region is in other words, I've got the active cell and the data area, remember we clicked inside of the area for 2020, we should just see results for Idaho. So I'll press F5 to run, alt, F11. And in fact that's what happened. And now I'll demonstrate how your filtering can operate in unexpected ways. I will first clear the filter by clicking the filter arrow, in cell B10 and click clear filter from state, but I will not clear the filter arrows. So in other words, filtering is still active for this range at the bottom. Now click any cell in the top area, I click cell B4 but it can be anyone in that smaller range. So, if I were to run the code as before, with no filter arrows displayed anywhere, then the filter would operate on the top range. That's the behavior that we saw a minute ago. However, if I press alt, F11, don't make any changes to the code and press F5 to run it, alt, F11 again, you see that the code filtered the bottom range. And that is an unexpected error that you might run into. However, we can get around that. First thing I'll do is to clear the filter. But again, as before I'm not going to clear the filter arrows here, I'm going to do it in VBA. So I'll press alt, F11 to move back to the visual basic editor. And here is where the line of code that I've commented out above comes into play. So I'll remove the single quote, so it's no longer commented and the code will run. The line of code, selection.autofilter, will turn auto filtering on or off. So in this case, auto filtering will be turned on for the top range. With the code in place, I'll press F5 to run it, alt, F11, and you see that the filter operated as we expected even though we still had filter arrows in the bottom range. So I know it jumped back and forth quite a bit in this movie, but the idea is that you want to filter based on the active range of cells. And it's always a good idea to turn filter arrows on or off, using selection.autofilter, to ensure that the area you want to filter is the one that your code works on.

Contents