VBA if statement to check auto filter Criteria

[dee101]

I need to check to see if a filter is set for a criteria and then do something based on that like this

if AutoFilter Field:=10, Criteria1:="1" then
Msgbox "criteria is 1"
else
Msgbox " not set to 1"

How can I do this?
Thanks

 

[davesexcel]

Worksheets("Sheet2").Range("E2:E1000").ClearContents
With Sheets("Sheet1").Range("A2").CurrentRegion
    .AutoFilter Field:=1, Criteria1:=Worksheets("Sheet2").Range("A2").Value
.Columns(5).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("E1")

.AutoFilter
End With

if you would use a range for the filter such as the above example code which filters a list that refers to sheet2 A2

the you could use a macro to see what is going to be filtered

Sub gh()
MsgBox Worksheets("Sheet2").Range("A2").Value
End Sub

 

[xld]

Public Sub Test()
If ShowFilter(Columns(1)) = "=1" Then
    MsgBox "criteria is 1"
Else
    MsgBox " not set to 1"
End If
End Sub


Public Function ShowFilter(rng As Range)
Dim oFilter As Filter
Dim sCriteria1 As String
Dim sCriteria2 As String
Dim sOperator As String
Dim nOp As Long
Dim nOff As Long
Dim rngFilter As Range
Dim sh As Worksheet

    Set sh = rng.Parent
    If sh.FilterMode = False Then
        ShowFilter = "No Active Filter"
        Exit Function
    End If
    Set rngFilter = sh.AutoFilter.Range

    If Intersect(rng.EntireColumn, rngFilter) Is Nothing Then
        ShowFilter = CVErr(xlErrRef)
    Else
        nOff = rng.Column - rngFilter.Columns(1).Column + 1
        If Not sh.AutoFilter.Filters(nOff).On Then
            ShowFilter = "No Conditions"
        Else
            Set oFilter = sh.AutoFilter.Filters(nOff)
            On Error Resume Next
            sCriteria1 = oFilter.Criteria1
            sCriteria2 = oFilter.Criteria2
            nOp = oFilter.Operator
            sOperator = ""
            If nOp = xlAnd Then
                sOperator = " And "
            ElseIf nOp = xlOr Then
                sOperator = " Or "
            End If
            ShowFilter = sCriteria1 & sOperator & sCriteria2
        End If
    End If
End Function

 

[dee101]

Thanks, I went with xld's on this one

 

[tonyr63]

Hi

I have a need for the ShowFilter Function but having copied the code with a test list i can never get the message Criteria is 1.
I have put a filter on my 1st column and the have steped the function throught to ShowFilter = sCriteria1 & sOperator & sCriteria2 and put a message box on the next line and ShowFilter = null.

What do I need to get the function to work?

I would like to copy all filtered items that are selected to new sheets for each criteria item but have no way to determine which criteria have been selected on a filtered list.