Excel VBA AutoFilter Multiple Criteria in Array or List

Excel VBA AutoFilter multiple criteria for Data Range

Learn MS Excel advanced filter option & accessing it from VBA with this simple tutorial. We will cover these topics.

  1. Excel Menu – To add filter for a range
  2. Excel VBA Macro code to add multiple criteria to Autofilter.
  3. VBA to Read visible rows after applying filter.
  4. VBA code to read selection criteria.

This is applicable for MS Excel 2007 Autofilter or any version above it.

1. How to Use Filter in Excel? – Menu option

To add a filter option, open a Excel workbook with table of data & follow these steps.

  1. Select the data range or table for which you need to add filter.
  2. Choose ‘Home’ in Excel menu.
  3. Select ‘Sort & Filter’ option in ‘Editing’ section.
  4. Press ‘Filter’. ( shortcut: ‘Alt + H + S + F’)

A drop down will be added at the first row or column header of selected data range or table.

Click on any of the drop down menu & choose a data in it. Data that only matches selection criteria in that column will we displayed and other rows will be hidden. To remove the filter, follow the same steps that we used to add it.

Once we add a filter, we can use it in different combination of scenarios as given below.

  • 1 Criteria: Click Filter drop down and enable check box for any one the criteria.
  • 2 Criteria: Click filter drop down, then choose “Text Filter” option for adding 2 criteria with logical operators (like equals, contains, greater than, less than etc.,).
  • More than 2: Either select each criteria manually from drop down or use VBA code mentioned below.

2. VBA Autofilter with Multiple Criteria

If we have to select more than 2 criteria or even 100s of values in consideration? We need to use some MS Excel advanced filter options or use the code below. With this we can autofilter data from a column using more than 2 selection criteria. The function we are going to use to automate this is:

AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

2.1 Autofilter in Sheet 1 based on Sheet 2 data

To understand how this works, create new workbook that has Sheet1 with data and Sheet2 with Criteria list as in the Input settings images below.

Data Worksheet (Sheet1) – Input 1

Excel VBA AutoFilter Multiple Criteria

Multiple Criteria (Sheet2) – Input 2

In sheet2 column1 Enter these values

  • Val1,
  • Val5,
  • Val6

2.2 Excel Macro Code to Autofilter data with Criteria list

  • Copy the below code and paste it in Excel VB Editor (Press Alt + F11 to view VBE)
  • Execute the code by pressing F5.
  • You will get the Results as shown in Output image.
Option Explicit
Sub Excel_VBA_AutoFilter_Using_Multiple_Criteria_List()
    'Variables Declacaration
    Dim Criteria_Val(100) As String
    Dim iRow As Integer

    'Loop Thru the List and Create Filter Criteria Array
    iRow = 0
    While Sheets(2).Cells(iRow + 1, 1) <> ""
        Criteria_Val(iRow) = Sheets(2).Cells(iRow + 1, 1)
        iRow = iRow + 1
    Wend

    'Multiple Criteria List is Created. Pass it as parameter to Filter
    Sheets(1).Range("A1:A10").AutoFilter Field:=1, Criteria1:=Criteria_Val, Operator:=xlFilterValues
End Sub

2.3 Output of Excel Advanced Filter with Multiple criteria

Once the code is executed, the sheet1 will show output as shown below, displaying only the data that meet the criteria in sheet2.

3. VBA to Read only Visible Data after Excel Filter

Use the below simple for to read the visible data range after a filter is being applied.

Option Explicit
Sub Call_Read_Visible_Range_Function()
    Dim wSht As Worksheet
    Set wSht = ThisWorkbook.Sheets("sheet1")
    If wSht.FilterMode = True Then
        VBA_Read_Visible_Rows 1, 2000, wSht
    End If
End Sub

Public Sub VBA_Read_Visible_Rows(startRow As Long, endRow As Long, wSht As Worksheet)

    '''''Method1 using Rows.Hidden Property'''''
    Dim iRow As Long, iCol As Long
    iCol = 1
    'Loop thru each row and find visible range
    For iRow = startRow To endRow
        If wSht.Rows(iRow).Hidden = False Then
            'Include code here to process visible rows table data
            MsgBox "(1) Read Data in Visible Row : " & wSht.Cells(iRow, iCol)
        End If
    Next iRow
    
    '''''Method2 using xlCellTypeVisible'''''
    Dim wRng As Range, iRng As Range
    Set wRng = wSht.Range("A" & startRow & ":A" & endRow)
    'Set Visible Range and loop through each cell in visible Range
    For Each iRng In wRng.SpecialCells(xlCellTypeVisible)
        'Enter Code here to read visible range data
        MsgBox "(2) Read Data in Visible Row" & iRng.Address
    Next iRng
    
End Sub

Since few rows will be hidden once the filter is applied, it is hard to read only the visible data through a macro. This vba macro reads only visible data in the table.

4. Excel VBA to get Autofilter selection Criteria

If we have already done with all the filtering & just want to read criteria list itself, then use the below simple code.

Sub VBA_Read_AutoFilter_Criteria()
    Dim wSht As Worksheet, iCrt As Variant, iFil As Filter, strCri As String
    Dim wRng As Range, iRng As Range, i As Long, j As Long
    
    'Assign worksheet & range
    Set wSht = ThisWorkbook.Sheets("sheet7")
    Set wRng = wSht.Range("A" & 1 & ":F" & 1)
    
    'Loop thru all columns with filters & read Criteria details
    For i = 1 To wRng.Parent.AutoFilter.Filters.Count
        Set iFil = wRng.Parent.AutoFilter.Filters(i)
        If iFil.On Then
            Select Case iFil.Operator
                Case xlOr
                    MsgBox "Values " & iFil.Criteria1 & " OR " & iFil.Criteria2
                    
                Case xlAnd
                    MsgBox "Values " & iFil.Criteria1 & " AND " & iFil.Criteria2
                    
                Case xlFilterValues
                    iCrt = iFil.Criteria1
                    'Function to get number of dimentions of a Variant or Array size of a variant
                    For j = 1 To Application.WorksheetFunction.CountA(iCrt)
                        strCri = strCri & "," & iCrt(j)
                    Next j
                    MsgBox "Multiple Criteria Array List: " & strCri
                    
                Case Else
                    MsgBox "Operator Used: " & iFil.Operator
            End Select
        End If
    Next i
End Sub

Note that this macro does not read data, instead it reads and displays on the list applied in criteria. For all possible operators used inside a filter, please check the syntax details in the next section.

MSOffice Excel Macro Autofilter Syntax

We will learn more about the parameters passed to the Excel advanced Filter method.

    1. Field – It is the Column Number which we want to Filter
    2. Criteria1 –  Data or List of Data or Criteria. In the above code, we concatenate the data from criteria list and pass it to the function.
    3. Operator – In the code we have used xlFilterValues since we filter a list of values from a range.
      • xlAnd – Logical AND of Criteria1 and Criteria2.
      • xlBottom10Items – Lowest-valued items displayed (number of items specified in Criteria1).
      • xlBottom10Percent – Lowest-valued items displayed (percentage specified in Criteria1).
      • xlFilterCellColor – Color of the cell
      • xlFilterDynamic – Dynamic filter
      • xlFilterFontColor – Color of the font
      • xlFilterIcon – Filter icon
      • xlFilterValues – Filter values
      • xlOr – Logical OR of Criteria1 or Criteria2.
      • xlTop10Items – Highest-valued items displayed (number of items specified in Criteria1).
      • xlTop10Percent – Highest-valued items displayed (percentage specified in Criteria1).
    4. Criteria2 – If we use any Local AND or OR in Operation, then Criteria 2 is used to compare with Criteria1.
    5. VisibleDropDown – Set it to True to view Filter Drop down or False to Hide Drop Down.

Reference:

Leave a Reply