How to use "excel vba autofilter exclude multiple values" and handle "multiple criteria"?

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

We can use the criteria syntax in the autofilter formula to include or exclude multiple values in our different columns. Below we are only including movies from 2010.

'Filter search on text.
Range("A1").AutoFilter Field:=2, Criteria1:="2010"

Filtering 2 criteria for 1 column

In the below filtering we are using multiple criteria filter in our autofilter syntax. We are excluding multiple values that does not fit in to our criteria year 2010 or 2016. And in our second filtering syntax we are telling our autofilter to include years greater than 2010 and less than 2016, thus excluding multiple values and rows with years outside 2010 and 2016.

'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

Now let's filter multiple criteria on multiple columns. Both column year for movies made in 2011 and IMDB ranking column for movies greater than 7.3 in ranking.

'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

Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba

Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python

If you have found this article or website helpful. Please show your support by visiting the shop below.

Pls Fix Thx Coffee Mug - Finance Blue

Buy Now