How to use Filters in VBA for single or multi column sorting?


This section will go through how to use the filter function in VBA, called Autofilter.

vba filters 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:


turn on autofilter


Filtering 1 criteria for 1 column

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


Filtering 2 criteria for 1 column

'Filter 2 years together
Range("A1").AutoFilter Field:=2, Criteria1:="2010", Operator:=xlOr, Criteria2:="2016"
filter 1 column autofilter with 2 criteria

'Filters an interval. movies between 2010 and 2016
Range("A1").AutoFilter Field:=2, Criteria1:=">2010", Operator:=xlAnd, Criteria2:="<2016"
autofilter range filtering

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
Filtering 2 criteria for 2 columns


Top 5 or Bottom 5

'Top 5 movies. Picks the newest movies that is.
Range("A1").AutoFilter Field:=2, Criteria1:="5", Operator:=xlTop10Items
Top 5 filtering
'Bottom 5 movies. Picks the oldest movies that is.
'Range("A1").AutoFilter Field:=2, Criteria1:="5", Operator:=xlBottom10Items
Bottom 5 filtering autofilter

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.

Looping in a filtered dataset

If we instead want to only loop through our filtered items we have to do the following:

Loop visible rows in filter vba

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

0 views0 comments