Auto Filter Object Example

This example demonstrates how to use the AutoFilter, Filters, and Filter objects by displaying the complete set of auto-filters currently in use:

Sub ShowAutoFilterCriteria()

Dim oAF As AutoFilter, oFlt As Filter

Dim sField As String

Dim sCritl As String, sCrit2 As String Dim sMsg As String, i As Integer 'Check if the sheet is not filtered, then exit If ActiveSheet.AutoFilterMode = False Then

MsgBox "The sheet does not have an AutoFilter" Exit Sub End If

'Get the sheet's AutoFilter object

Set oAF = ActiveSheet.AutoFilter 'Loop through the Filters of the AutoFilter

For i = 1 To oAF.Filters.Count 'Get the field name from the first row of the AutoFilter range sField = oAF.Range.Cells(1, i).Value 'Get the Filter object

Set oFlt = oAF.Filters(i) 'If it is on, then get the standard filter criteria If oFlt.On Then sMsg = sMsg & vbCrLf & sField & oFlt.Criterial 'If it's a special filter, show it

Select Case oFlt.Operator Case xlAnd sMsg = sMsg & " And " & sField & oFlt.Criteria2 Case xlOr sMsg = sMsg & " Or " & sField & oFlt.Criteria2 Case xlBottomlOItems sMsg = sMsg & " (bottom 10 items)" Case xlBottomlOPercent sMsg = sMsg & " (bottom 10%)" Case xlToplOItems sMsg = sMsg & " (top 10 items)" Case xlTop10Percent sMsg = sMsg & " (top 10%)" End Select End If Next

'Construct a no filters message if no filters applied If sMsg = "" Then sMsg = "The range " & oAF.Range.Address & " is not filtered." Else

'Construct a message showing any filters that are applied sMsg = "The range " & oAF.Range.Address & " is filtered by:" & sMsg End If 'Display the message

MsgBox sMsg End Sub

0 0

Post a comment