Got any Excel Questions? Free Excel Help
See Also: AutoFilters via User Interface | Display Excel AutoFilter Criteria
VBA & AutoFilters
AutoFilter provides us with a MUCH faster alternative to loops of all kinds.
In the majority of cases it's faster and more efficient to use one
of Excel's built in features as apposed to re-inventing the wheel with VBA code.
This is why those that have learnt Excel from the ground-up know what native features
Excel has to offer. While those only familiar with VB/VBA tend to be the ones who
re-invent the wheel.
Ok, the first thing we need to know is how to apply AutoFilter to a range. When we do apply AutoFilter via VBA one SHOULD always turn the off any current filters and remove them completely. Why not check if the AutoFilter is already in place and go from there? The answer is simple, while we can determine if AutoFilter has/is on a specific Worksheet, we cannot guarantee (with extra checking) that it is in use on the range we need! For example, we could use the code below to check.
Sub CheckForAutoFilters() If ActiveSheet.AutoFilterMode = True Then MsgBox "They are visible" Else MsgBox "They are not visible" End If End Sub
From the code above we will know if AutoFilters are visible, but not necessarily in Filter mode (more on that soon). However, we cannot tell if the AutoFilterMode is applied to the correct range. Let's now see how we can determine if the AutoFilters and in use and are being used to filter down.
Sub CheckForAutoFilters2() With ActiveSheet If .AutoFilterMode = True And .FilterMode = True Then MsgBox "They are visible and in use" ElseIf .AutoFilterMode = True Then MsgBox "They are visible but not in use" Else MsgBox "They are not visible or in use" End If End With End Sub
As you can see, we have used the FilterMode Property of the Worksheet to determine whether the AutoFilters are filtering data down. So, in summary, AutoFilterMode tells us if the AutoFilter arrows are visible and FilterMode tells us if they are in use. However, as I mentioned above this does not tell us which range has had AutoFilter applied. So, with this in mind, we are better off simply removing any existing Autofilter and then applying them to our required range. Here is how, assuming we want A1:D1 to have the AutoFilters.
Sub ApplyAutoFilters() With ActiveSheet .AutoFilterMode = False .Range("A1:D1").AutoFilter End With End Sub
Another advantage to applying AutoFilter is this manner is that no error occurs if AutoFilterMode is already false. By the way, we cannot use: AutoFilterMode = True to apply AutoFilters. To apply AutoFilter (at this time with no criteria) we would use Range("A1:D1").AutoFilter. If we are to first check the range that AutoFilter is applied to, we would use code like below;
Sub IsAutoFiltersOnRightRange() With ActiveSheet If .AutoFilterMode = True Then MsgBox .AutoFilter.Range.Address Else MsgBox "AutoFilters are not on" End If End With End Sub
In my mind though, this code is superfluous when compared with simply removing and applying AutoFilters. Let's now look at how we apply AutoFilter to a SINGLE cell in a range. If we had our table in the range A1:D200 on the Active sheet and we used the "ApplyAutoFilters" Procedure with .Range("A1").AutoFilter we would likely end up with AutoFilter applied to ALL contiguous headings across row 1. This due to the fact that Excel will detect the contiguous headings across row 1 and assume that we want all headings to have AutoFilters. We can force Excel to not do this by specifying a 2 row single column range. For example;
Sub ApplyAutoFiltersToOneCell() With ActiveSheet .AutoFilterMode = False .Range("A1:A2").AutoFilter End With End Sub
Next: Applying AutoFilter Criteria in VBA
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates