Examples
Basic example
The following simple example tries to demonstrate how an
autoFilter object might be used.
The example also prints the values the worksheet's
.autoFilterMode and .filterMode properties a few times. The meaning of these properties is: -
autoFilterMode, iftrue: drop down triangles are shown -
filterMode, iftrueat least one filter is active, thus some rows might be hidden.
option explicit
sub main() ' {
dim sh as worksheet : set sh = activeSheet
dim rng as range
set rng = createTestData(sh)
printFilterProperties sh ' autoFilterMode: False, filterMode: False
'
' Turn on the filters on the ranges.
' Note: the word «autoFilter» is a
' method when applied to a range,
' but a property when applied to
' a worksheet
'
rng.autoFilter
printFilterProperties sh ' autoFilterMode: True, filterMode: False
'
' First criteria: value of colTwo
' needs to be between 50 and 100:
'
rng.autoFilter field := 2, _
criteria1 := ">50" , _
operator := xlAnd , _
criteria2 := "<100"
printFilterProperties sh ' autoFilterMode: True, filterMode: True
'
' Second critera: value of first column
' needs to start with the letter «U»:
'
rng.autofilter field := 1, _
criteria1 := "=U*"
sh.usedRange.columns.autoFit
end sub ' }
function createTestData(sh as worksheet) as range ' {
' sh.usedRange.clearFormats
' sh.usedRange.clearContents
dim r, c as long : r = 0 : c = 1
with sh ' {
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("colOne", "colTwo", "colThree" , "colFour")
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("AB" , 130 , #2014-06-02#, 21.4)
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("UVW" , 99 , #2010-05-07#, 17.2)
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("PQ" , 42 , #2020-02-17#, 38.0)
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("XYZ" , 111 , #2018-12-03#, 1.1)
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("DEFG" , 15 , #2017-04-28#, 5.9)
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("UTA" , 128 , #2011-06-03#, 33.9)
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("XYZ" , 111 , #2018-12-03#, 1.1)
r = r + 1 : .range( .cells(r, c), .cells(r, c+3) ).value = array("CLM" , 68 , #2021-04-19#, 65.3)
set createTestData = .range(.cells(1, c), .cells(r, c+3) )
end with ' }
end function ' }
sub printFilterProperties(sh as worksheet) ' {
debug.print "autoFilterMode: " & sh.autoFilterMode & ", filterMode: " & sh.filterMode
end sub ' }
Github repository about-MS-Office-object-model, path: /Excel/AutoFilter/basics.bas
The only record that satisfies the selected criteria is the second one: 
See also this example that demonstrates how auto filters can be used on
listObject objects. At most one autoFilter per worksheet
Excel allows at most one active
autoFilter per worksheet. If the autofilter is active can be queried in VBA with an expression similar to not activeSheet.autoFilter is nothing
This expression evaluates to
true if an auto filter is in place and to false if not.
Note that an autofilter can be active even if no rows are filtered or any criterias were defined.
If multiple regions (ranges) need to be filtered, list objects (aka tables) might be used.
Showing all records with VBA
In order to show all records that might previously have been filtered, the
showAllData method can be used: activeSheet.showAllData
This is effectively the same as clicking Data -> Sort & filter -> Clear on the Ribbon.
Required header
As far as I see it, a header is required for an
autoFilter, at least when defining it. Problems with sum function
When using the
=sum(…) worksheet function right below (and adjacent) to the filtered range, it seems that Excel tries to incluce this sum() into the range (and thus is hidden after applying a criteria to the filter). Print filtered columns
The following simple function prints (
debug.print) the numbers of the columns where a filter is applied: sub printFilteredColumns(byVal ws as worksheet) ' {
if not ws.filterMode then ' {
debug.print "no columns are filtered"
exit sub
end if ' }
dim col as long
for col = 1 to ws.autoFilter.filters.count ' {
if ws.autoFilter.filters(col).on then
debug.print(col)
end if
next col ' }
end sub ' }
The function can be called from the immediate window like so
printFilteredColumns activeSheet