» Display AutoFilter criteria after filtering using VBA in Microsoft Excel

CATEGORY - General Topics in VBA VERSION - All Microsoft Excel Versions

Question:
After filtering with AutoFilter, titles of filtered data with criteria are to be displayed at the bottom of the sheet

Answer:
Place the code below into the standard module.





Sub FilterCriteria()

   Dim intRow As Integer, intCol As Integer

   intRow = Range("A1").CurrentRegion.Rows.Count + 2

   intCol = 1

   Do Until IsEmpty(Cells(1, intCol))

      With ActiveSheet.AutoFilter.Filters(intCol)

         If .On Then

            Cells(intRow, intCol).Value = .Criteria1

         End If

      End With

      intCol = intCol + 1

   Loop

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

      Cells(intRow + 1, 1)

End Sub