Excel Filter Data

In this post we see how to filter data in Excel using VBA macros. As an example we use football (soccer) scores data. Get sports data in Excel here or see results, statistics, and predictions for upcoming games on XGoals (Football Predictions).

Check if filtering is enabled and data is filtered

We use properties of the Worksheet object to check if filtering is on, and if so, whether the data has been filtered. The AutoFilterMode property is used to know if filtering is enabled for a given worksheet (True or False).

 Dim IsFilterOn As Boolean
 IsFilterOn = ActiveSheet.AutoFilterMode

Furthermore, the FilterMode property tells whether the data has been filtered (True) or not (False). If the data has been filtered, the method ShowAllData can be used to show all the data again. It is good practice to use the following condition before starting the filter operation to ensure we work with the whole data set.

 With ActiveSheet
     If .FilterMode = True Then .ShowAllData
     'start filter operation
     '…
 End With

It’s also common to use the method ShowAllData right at the end of the macro after performing the filter operations to make sure the whole data set is visible again.

 

Filter field and criteria

In order to filter data in a worksheet we need to specify a range. We can specify a fixed range address or refer to the used range in a given sheet. Using the AutoFilter method alone for a range, switches the filter on and off.

 ActiveSheet.UsedRange.AutoFilter

The method AutoFilter accepts several arguments to determine the fields and criteria to filter the data. In the example below, we filter the football scores data for a given team playing home (column C) in the Spanish premier league.

 With ActiveSheet.UsedRange
     .AutoFilter Field:=3, Criteria1:="VALENCIA"
 End With

 

The criteria can also be a number or a condition, so for example, if we want to filter the games where Valencia scored 2 or more goals, that would be done setting the criteria as indicated below. This would be another line of code after the one before.

 .AutoFilter Field:=7, Criteria1:=">=2"

 

 

Filter multiple criteria

We can use operators to connect various criteria in the same line of code. For example, the code below filters the data to show all games where the home team is either Real Madrid or Barcelona.

 .AutoFilter Field:=3, Criteria1:="BARCELONA", Operator:=xlOr, Criteria2:="REAL MADRID"

 

 

That’s often used to filter data by date. But in this case we need to use the operator xlAnd. The code below filters the data to show the games played in August 2022 (between 1^st and 31^st of August 2022).

 .AutoFilter Field:=4, Criteria1:=">=08/01/2022", Operator:=xlAnd, Criteria2:="<=08/31/2022"

 

However, that only allows to use two criteria (Criteria1 and Criteria2, there is no Criteria3 or further). We need to define an array of values when filtering by more than two criteria. The code below filters the data to show all games where Valencia, Elche, or Betis are the home team.

 .AutoFilter field:=6, Criteria1:=Array("VALENCIA", "ELCHE", "BETIS"), Operator:=xlFilterValues

 

Note that the operator used in this case is xlFilterValues. There are many other operators that allow to filter by cell or font color, top or bottom 10 values, and more. For each of those scenarios, a relevant Criteria1 argument needs to be used.

 

Filter multiple fields (columns)

We use the method AutoFilter as many times as need to further filter the data. The code below filters the data to show the games that ended 0-0 for Barcelona playing home.

 With ActiveSheet.UsedRange
     .AutoFilter field:=10, Criteria1:=0
     .AutoFilter field:=11, Criteria1:=0
     .AutoFilter field:=6, Criteria1:="BARCELONA"
 End With

 

As long as ShowAllData is not used, filtering keeps hiding rows that do not meet the filter criteria. Thus, we will apply filters up to the point where we want to do something with the filtered data (see next). Then we show all data and apply new filters as needed.

 

Copy filtered data

Among other things, we may want to copy the filtered data to another sheet or another workbook in order to share it, perform some operations, create reports or charts, etc. There are several options to copy the filtered data. One way is using SpecialCells to get visible cells only. The code below copies whatever data has been filtered in Sheet1 and pastes it into Sheet2.

 Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Copy
 Sheet2.Paste

 

That copies the whole data set, but we can also specify the range or columns that we want to copy/paste.

There is a more straight forward way to copy/paste filtered data. This method targets the Range property of the AutoFilter, which is a method of the Worksheet object. The code below copies the data filtered in Sheet1 and pastes it in Sheet2.

 Sheet1.AutoFilter.Range.Copy
 Sheet2.Paste

   

Loop through filtered data

We can also loop through filtered data using SpecialCells to target visible cells only, and then get certain info for each of the filtered rows. The example below displays the row number for each of the filtered entries.

 With ActiveSheet.UsedRange
     For Each r In .SpecialCells(xlCellTypeVisible).Rows
         MsgBox r.Row
     Next r
 End With

 

We can also get certain values and make calculations. But for simple calculations like count, sum, average, we do not really need a loop, we can use functions instead (see next).

 

Count filtered entries

We can use worksheet functions to count, sum, get the average, and other operations for a specific column in the filtered dataset. The code below counts the number of rows that have been filtered (excluding the header row). For Count, It does not matter which column it targets (it targets column 1 in this example).

 With ActiveSheet.UsedRange
     result = WorksheetFunction.CountA(.Columns(1).SpecialCells(xlCellTypeVisible)) – 1
     MsgBox result
 End With

 

We need to target the right column when using other functions such as Sum or Average, as those will make the calculation based on the filtered values in that column.

We can achieve the same outcome using the function Subtotal. In this case, we do not need to use SpecialCells because Subtotal targets only visible or all cells depending on the value of the first argument.

 result = WorksheetFunction.Subtotal(3, .Columns(1)) - 1

The first argument also determines the type of operation (count, sum, average, etc). See more about the Subtotal function in this other page.