Display AutoFilter criteria after filtering using VBA in Microsoft Excel

In this article, we will create a macro to display the criteria used in auto filter.

Raw data consists of client details, which includes Name, Phone number, Email id and Company name.

We have applied a filter in the Company column and now we want to determine name of the complany on which the filter is applied.

We will click on “Separating filter data” button to get criteria for which the filter is applied.

Code Explanation

IntRow = Range("A10").CurrentRegion.Rows.Count + 12

The above code is used to get a row number, where output should be displayed.

ActiveSheet.AutoFilter.Filters(IntCol) .On

The above code is used to check whether the filter is applied on the particular column.

For Each StringValue In .Criteria1

MainString = MainString + Mid(StringValue, 2) + "|"

Next

The above code is used to create a string which consists of all the criteria values, used in the filter.

Range("A10").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _

Cells(IntRow + 1, 1)

The above code is used to copy visible rows to the specified destination.

Please follow below for the code

Option Explicit

Sub FilterCriteria()

'Declaring variables
Dim IntRow, IntCol As Integer
Dim MainString, StringValue As Variant

'Initializing the row and column number
IntRow = Range("A10").CurrentRegion.Rows.Count + 12
IntCol = 1

'Looping through all the cells until blank cell is encountered in the 10th row
Do Until IsEmpty(Cells(10, IntCol))
    
    With ActiveSheet.AutoFilter.Filters(IntCol)
        'Checking whether filter is applied on the column
        If .On Then
            
            MainString = "Filter On Column no. " & IntCol & " on values : "
            
            'Creating text which consists of values used in the filter
            For Each StringValue In .Criteria1
                MainString = MainString + Mid(StringValue, 2) + "|"
            Next
            
            'Assigning value to cell
            Cells(IntRow, 1).Value = MainString
            
            Exit Do
            
        End If
        
    End With
    
    IntCol = IntCol + 1
Loop

'Copying the visible cells to row after the filter data
Range("A10").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
   Cells(IntRow + 1, 1)

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com