Menu
0

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.

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

0 views0 comments