IF Formula Challenge

In this video we’re going to look at how to apply filters to a table with VBA. So here I have an example, just a very simple data set again. And we’re going to look at how to apply filters just like we would with the filter drop down menus here. We can also do this with VBA using the auto filters. So let’s jump into the VB editor. And I am in this filter table macro here. And again same set up here, we’ll declare our list object, and then set our list object to the first table on the sheet. And then we’re going to use the auto filters in VBA. And these are these same filters we’d use on a regular range, and same set of properties and methods here with the auto filter that we would use on a regular range in a work sheet. We can also do that with tables. And again there are some advantages with filters on tables. One of them is we have that self contained kind of sheet within a sheet, or range within a sheet that has all of our table data and we don’t necessarily have to worry about finding the last use row and defining that range when we’re turning our auto filters on or off and things like that. So let’s go and start stepping through this code. And that again will just set our list object. So this line here allows us to turn the auto filter, the filter drop down buttons on or off. So if we show auto filter equals true, then those buttons will be turned on. They’re already turned on. But we could set this equal to false to turn those off if we wanted to. So I hit F8 there. This next line of code will just clear the filters within the table. So the show all data is that method there to just clear the filter. So I hit F8, you can see our filters now have been cleared over here. In our table, no filters applied there. And then this next line will allow us to apply a filter. So when we do that we use the auto filter, and again we can just type out this line of code here, so again lo.range. So the Range.AutoFilter. Auto filter is a method and it has some parameters here. If we hit the space bar we’ll see we get the parameters. The first is the field. So we have to [stress 00:02:16] by the field which is the column that we want to apply the auto filter to. And then the criteria. So we have criteria one right here, we have an operator and then a criteria two. And also whether the drop down is visible or not. So to keep it simple, you can see that these are optional parameters here, they have the square brackets around them. But we do want to reference a field and a criteria. And that’s exactly what I’ve done in this line above right here. We’ve referenced the field, and the field again is the column number. And that’s the column number of the table or of the auto filter range. So this would be column four, which if we look at our table over here that’s going to be the region column, so column one, two, three, four is the region column. So we’ll just go ahead and apply that filter. Again, very simple filter, let me delete this line of code here, and we’ll hit F8 to run that one. So now we have applied a filter for the east region here in column E or the fourth column in our table. We can also reference the column by name instead of by number. Or we could return the column number with a list columns property. And that’s exactly what we’re doing in this portion of the code right here. So we’re still setting the field to a number, it’s just the index number of this specific column. So really we’re just referencing the column lo.listColumns, bring up that column’s property. Of course we can reference a column by its name, like we’ve done here. And then the index with return the column number within the table. That’s exactly what that’s doing there. If we hover over it typically we’ll see a four come up. It’s not coming up right now. Oh! There we go. Or three. Yeah, customer name is column three. So we got column three which will be our field. And then we’re setting the criteria equal to company G. So we’re just going to filter for company G. So that would be customer name column right here. So if I hit F8 now, that will set that filter there to company G. Now these are very simple examples. I’ll just go ahead and stop running this macro here. If your filters are more complex, maybe contain multiple items, a date range, a greater than or less than a specific value. Then great to use the macro recorder to help get that code for the filter criteria. So we’ll take a look at how we can do that. So first thing I’m going to do is just clear my filters here Alt+A+C or go to the data tab, clear filters and then we’ll apply a more advanced filter. So first thing we’ll do is turn the macro recorder on. So I’ll just click the macro recorder button here. Put a new macro in this workbook and hit okay. And so we could go to our order day column. Maybe want to do a date filter before a specific date. So in this case we could say it’s maybe before 1/20/2014, I’ll just say something like that, hit okay. We could also do a filter for multiple criteria here. Maybe just the east and north, but not west, something like that. Hit okay there. And you could also do a number filter here for revenue and just continue to apply different filters. Whatever filter you need, continue to apply those and record it. And then we’ll jump back over to the VB editor and look at the code that’s been recorded. It’s right here in module one. Let’s open this a little bit. So here is our code right here. So we can see now in this line we’re still doing the auto filter on our list object. So our list table here. Field two would be the second column. And then our criteria here is anything less than 1/20/2014, that date right there. This operator Excel and that might be optional. We might not need that because there’s not multiple criteria here. I’m not exactly sure but you can just keep it there and then test whether or not you need that. In this case here we have another one for the criteria equals east or criteria equals north. So that’s one with kind of those two filters applied. If you wanted to see something with more than two filters applied to it, you can just go back to Excel, we can clear these filters, maybe here where we just want to see all companies, except for AA and C, hit okay. Our macro recorder is still on, switching back to VB editor, and now we can see here is a little more advanced filtering. Where we have an array with all the company names that we do want a filter for. So there’s all those companies there. They’re referenced in an array for criteria one right here. So criteria one is going to reference that array, or that list. It’s really just a list of all those different company names that we want to filter for. We also have that operator parameter there that’s just telling it to filter for values. And so the point here is that we can just take this code that the macro recorder has created for us and just copy and paste it into our existing macro if we’d like. We can take the whole code or we already have everything up to the auto filter, so maybe we just want to take this portion here. I’ll hit Ctrl+C to copy and then we’ll go over here to our existing code and we can create a new row or a new line of code down here with some of that code. So we’ll say this case here, we already have our list object set up to do that auto filter. Just put that line of code in front of it, and now this line here will just run that filter on those company names, that list of company names that’s in that array. So again, the point is that, as your filters get more complex, just use the macro recorder to grab that code and then modify it as you need. But that’s how to apply filters with VBA to our tables, same methodology works there if you’re not using an Excel table, if you’re just using a regular range on your worksheet, and this is not a table, same methodology applies with the auto filters and it’s a great tool ’cause it’s saved us a lot of time with doing things like copying and pasting specific cells instead of looping through every single cell in the sheet to find company G. We could just apply a filter for company G using the VBA code then just copy and paste this visible range or something like that. So definitely those filters can save us a lot of time when we’re working with data.