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.
IntRow = Range(“A10″).CurrentRegion.Rows.Count + 12
The above code is used to get a row number, where output should be displayed.
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) + “|”
The above code is used to create a string which consists of all the criteria values, used in the filter.
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
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 firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.