This section will go through how to use the filter function in VBA, called Autofilter.
Filtering is very practical when working with bigger datasets. Instead of
creating loops that will go through the table multiple times to look for something it could be very useful and time saving(!!) to just use the autofilter function instead.
Autofilter has the following syntax where expression is a range. Expression.AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)
Field = integer offset for your column. First column being 1.
Criteria1 = string to be compared with the data.
Operator = XlAutoFilterOperator
And = xlAnd
Or = xlOr
xlTop10Items = top items, can also use this for top 5 like below.
xlBottom10Items = bottom 10, can also use this for bottom 5.
xlFilterCellColor = the cell color
xlFilterFontColor = the font color
Find more on.. Microsoft's webpage.
SubField = Default is just the display value, but otherwise this field can be used to specify a data type on which the user wishes to apply the criteria.
VisibleDropDown = True or False, shows the filtericons. default is true so user knows it is filtered.
Turn ON or OFF the filter
'Turn ON or OFF the filter. 'Only necessary to specify A1 as that is my starting cell for the cells group. Range("A1").AutoFilter 'Turn on autofilter if off If Not ActiveSheet.AutoFilterMode Then Range("A1").AutoFilter End If 'Turn off autofilter if on. This acts on the entire sheet however. If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False End If
How my worksheet looks like with the filter ON:
Filtering 1 criteria for 1 column
'Filter search on text. Range("A1").AutoFilter Field:=2, Criteria1:="2010"
Filtering 2 criteria for 1 column
'Filter 2 years together Range("A1").AutoFilter Field:=2, Criteria1:="2010", Operator:=xlOr, Criteria2:="2016"
'Filters an interval. movies between 2010 and 2016 Range("A1").AutoFilter Field:=2, Criteria1:=">2010", Operator:=xlAnd, Criteria2:="<2016"
Filtering 2 criteria for 2 columns
'Note that we have 2 movies for 2011 --> "Margin Call" and "Too Big to Fail" but only "Too Big to Fail" has an IMDB ranking over 7.1 which leaves us with only "Too Big to Fail". With Range("A1") .AutoFilter Field:=2, Criteria1:="2011" .AutoFilter Field:=3, Criteria1:=">7.1" End With
Top 5 or Bottom 5
'Top 5 movies. Picks the newest movies that is. Range("A1").AutoFilter Field:=2, Criteria1:="5", Operator:=xlTop10Items
'Bottom 5 movies. Picks the oldest movies that is. 'Range("A1").AutoFilter Field:=2, Criteria1:="5", Operator:=xlBottom10Items
Looping in a filtered dataset
If we loop like we normally do it will look like this: All items regardless filtered or not will be included in our loop. sometimes good, and sometimes not.
If we instead want to only loop through our filtered items we have to do the following:
In the above example I am setting my defined loop range to start from A2. Alternatively I can also set my VisRange variable like the following.
'Same as above, but printed out the range instead of using the Rng variable. Set VisRange = ActiveSheet.AutoFilter.Range.Range("A2:A11").SpecialCells(xlCellTypeVisible).Cells If I want to loop through the entire column however and include my title I can instead use the following syntax. 'For Column, note that header is included. Set VisRange = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells