I got a problem using AutoFilter with VBA in Excel.
Re: Date Filter not working in PivotTable This is the oddest thing I have ever seen. I just opened the file from the attachment link and it allows me to use date filters. When I open the exact same file saved on my desktop, it doesn't. Thank you for checking. Or functionality is not lost, but the workbook might not look or work exactly the. What it means In Excel 2007 or later, you can apply filters that are not supported in Excel 97-2003. In Excel 97-2003, you can then filter the data manually.
It works well for regular filters, but filtering the date column does not work as intended.The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.
Here is my code:
Anyone has an idea? It seems to be a common problem, but I have not found a solution.
Thanks in advance.
Edit: Just to add, when I macro record it and run the recorded macro, it does not work either.
Spurious
SpuriousSpurious
7 Answers
Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.
Sometimes I have found that one can use the numeric value of the date, especially when dealing with 'dates between'
Note that the above need to be 'real dates' and not strings that look like dates. Even a single 'string date' will mess things up.
Ron RosenfeldRon Rosenfeld
Autofilter()
works with 'universal' format yyyy-mm-dd
, i.e.:It's better because Excel can't 'understand' it wrong . If you use
mm/dd/yyyy
or dd/mm/yyyy
Excel can fit 02/jan as 01/feb.see: http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm
The Rules for Working with Excel (International Issue)
- When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.
Edit:
We can create an universal Function using
Application.International
like:MakahMakah
you need to convert the format to the american format, like:'>' & Format([datecell], 'mm/dd/yyyy')VBA does not understand another format.
Karlos HenriqueKarlos Henrique
This syntax works for me:
Hint obtained through a macro registration
Mago FabianMago Fabian
Karlos Henrique,Thanks for suggesting Format([datecell], 'mm/dd/yyyy').It works in my file.My previous code is :
My modified code is :
Thanks.
pmykpmyk
Match your 'dd-mm-yyy' to the format of the column, so if you have '16-Aug-16' as your source data formatting then make the filter as 'dd-mmm-yy'
Graham PGraham P
One solution is to set up correctly the format of the target column. If its formatted as 'custom', even if it looks like a date, it won't work.
You are asking VBA to filter based on a date, therefore the format of the columns has to be a date too.
Andrés RoldánAndrés Roldán
Not the answer you're looking for? Browse other questions tagged excelvbadateautofilter or ask your own question.
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Massive spreadsheets are difficult to deal with. Searching for the things you want can be difficult. Scrolling through thousands of rows is even worse.
Excel’s filtering abilities can help.
By using AutoFilter and advanced filters, you can whittle the visible data down to just the information you want to see. And that makes your job easier.
Get your FREE exercise file
There’s a lot of filter functionality to go over, and it’ll be much easier to learn if you can follow along.
Grab our free example workbook below and work through it with us!
Using Excel’s AutoFilter
Excel makes it incredibly easy to filter your data. Open up the example workbook and you’ll see what I mean.
On the first page, you’ll see that we have a list of car makes, models, model years, and values. We can use the AutoFilter to get all sorts of information from these columns.
To enable the AutoFilter, click on any cell in the table, and click the Filter button in the Data tab of the Ribbon. You’ll see some arrows appear in your header row:
If that ever doesn’t work, highlight the cells in the range you’d like to filter, then click the Filter button.
Those little arrows might not seem like much, but they open up a lot of power.
Click on the filter arrow in the Make column, and you’ll see lots of options:
At the top, you’ll see buttons for sorting your data alphabetically (or in reverse) by the selected column.
There’s also Sort by Color, which we covered in our article on sorting, and Text Filters, which we’ll get to in a moment.
For now, though, look at the list at the bottom of the pop-up menu. You’ll see a list of the different car makes in the list, each with a checkbox next to its name.
Unchecking one of those boxes filters out that particular make from the list. Try unchecking “Honda,” and you’ll see that the first entry in the list disappears:
Go back in, click on that same box to add a checkmark, and you’ll see that the first entry comes back.
Now, try removing the check from the “(Select All)” box.
This removes the check from every entry in the list. Excel won’t let you click OK if you have a completely empty list, though.
So check the box for BMW and click OK.
You can see in the image above that the filter arrow changed to an image of a filter. This reminds you which columns have applied filters at any given time.
Click Clear Filter From “Make” in the menu, and you’ll have every one of the original entries back.
Take note of the search box directly above the list, as well.
If you start typing the value you’re looking for, Excel will show you the matching values:
Filtering by color
If you use colors to code your data (perhaps with the always-useful conditional formatting), filtering by color is a great feature to know about.
And it’s as easy as you’d expect.
I’ve filled a few cells with random colors to show you how it works.
I used three colors here, but filtering works with any number of different colors.
To view only a specific color in your list, click the filter arrow, hover over Filter by Color, and select a color:
When you click on a color, you’ll see only the cells with a matching fill color:
You can also filter for cells that don’t have a background color. Just select No Fill from the Filter by Color options.
To view all your cells again, click Clear Filter From “Make” (or the corresponding name of your own column), and you’ll have the entire list again.
Pro tip: filtering by multiple colors
You might notice that you don’t have the option of selecting multiple colors. This is one place where filtering by color falls short of standard filtering. But you can do a bit of a workaround to get a similar effect.
To get started, open the filter menu and go to Sort by Color > Custom Sort. Use the options here to sort the cell colors in the order you want them (if you need a refresher, check out our article on sorting).
When you have them set up how you’d like, click OK.
Once you’ve done that, select the rows that you don’t want to see, right-click, and select Hide.
It’s not an especially elegant solution, but it works.
Filtering for specific text
AutoFilter also gives you the ability to filter for specific strings of text. If you want to find all entries that start with a B, for example, you can. Or every entry that doesn’t contain the string “gh67.”
To find these options, click the filter arrow, click Text Filters, and select an option. We’ll select Begins With…
Once you’ve made a selection, you’ll see the Custom AutoFilter box:
From here, select the type of filter you want, enter the relevant search information, and click OK.
For example, if we want to find the values in the Make column that start with B or C, we’d run the AutoFilter like this:
After we hit OK, we get a list of all the cars whose makes start with B or C:
The other custom text AutoFilter options work the same way. Select the type of filter you want, enter the relevant letters or words, and hit OK.
And remember that if you want to access more filtering options than those available in the Text Filters menu, click Custom Filter… at the bottom.
You can also filter for cells that don’t have a background color. Just select No Fill from the Filter by Color options.
To view all your cells again, click Clear Filter From “Make” (or the corresponding name of your own column), and you’ll have the entire list again.
Using number filters
Like the text filters, you can access number filters that let you filter for cells that meet certain criteria.
Click on the arrow in the Value column, and select Number Filters.
As you can see, there are plenty of options, most of which are self-explanatory. The most interesting options are Top 10, Above Average, and Below Average.
And, as with the text filters, you can use the Custom Filter… option to create your own filter according to your specifications.
Try creating a few number filters to find cars that are worth more than $20,000 and cars that are worth between $15,000 and $17,000.
Advanced filters
With these powerful tools, you might be wondering what more you could possibly get from “advanced” filters. And there are actually quite a few things.
Click on the Advanced button in the Sort & Filter section to see what I mean.
You’ll see a new window with some unfamiliar-sounding fields.
As you might expect, the List range is the data that you want filtered.
Criteria range, however, is something we haven’t dealt with yet. Instead of simply clicking on the items you’d like to see in your filtered list, advanced filters require that you create a criteria range.
Here’s how we’ll set that up. In cells F1, G1, and H1, type “Model,” “Year,” and “Value.”
We can now use these columns to create our filter criteria.
For example, if we type “Fit” in the Model column, we will filter our list so that we only see cars with the model name “Fit.”
To engage the filter, click on Advanced again, click into the Criteria range box, and select F1:H2.
Note that you can type in the criteria range or click-and-drag to select it.
Once you’re done, hit OK to filter.
Try it for yourself! Type in a model name, then open the advanced filter dialog and select your criteria range. When you hit OK, you’ll only see a subset that matches your criteria.
You can also use logical statements to filter.
For example, you can put “>30000” in the Value column to filter for cars that are worth more than $30,000.
And you can use wildcards for text, too. “M*” would return models that start with M, for example.
Pro tip: combining criteria
Now that you understand the basics of using a criteria range, we can talk about some of the more powerful things you can do.
Including two different criteria on the same row, for example, is equivalent to filtering for an AND logical statement:
As you can see in the screenshot above, we’ve filtered for any car with a model name that starts with M* AND is worth over $30,000.
If you put criteria in different rows, it functions as an OR statement:
In the image above, we’re filtering for any cars that have the model name “Fit,” OR that were made in 2000, OR that are worth less than $50,000.
Combining the AND and OR functionalities is where advanced filters get really cool.
Here’s an example:
This filter will return any car that was made in 2000 AND is worth more than $40,000, OR has a name that starts with M AND is worth more than $40,000.
You can combine many different AND and OR statements in this manner.
You probably noticed the Action options at the top of the advanced filter dialog. There are two options: Filter the list, in-place; and Copy to another location.
We’ve been using the filter-in-place option, but if you want to copy the results of your filter to a new location, select the radio button next to the latter option.
You’ll also need to tell Excel where to put the copied list. That’s what the final field, Copy to, is for. Type or click to tell Excel where it should put the newly copied list.
Excel only lets you copy a filtered list to the current sheet. If you want it on another sheet, run the filter, cut the filtered list, and paste it on the sheet where you’d like it.
Finally, there’s the Unique records only checkbox at the bottom. Check this if you want to remove duplicates from your copied list.
And don’t forget: to clear your advanced filter, click the Clear button in the Sort & Filter section of the Ribbon.
Filter like an Excel master
We know that’s a lot of information to take in. But that’s because Excel’s filtering capabilities are second-to-none.
It does a lot of the work for you, but if you’re willing to put in the time to learn how to properly use filters, you’ll be able to slice and dice your data in any way necessary.
Especially if you get good at using advanced filters. With all the capabilities of AutoFilter, it might seem unnecessary, but there may come a day when it will save you hours of extra work. So don’t forget about it.
As with anything in Excel, filtering takes a while to get the hang of. But stick with it, and you’ll seriously step up your Excel game!