CODEWITHSUNDEEP
excelvba
InfinityBlaze
InfinityBlaze

Reputation: 3

VBA to stop macro with msg box if no criteria found in auto filter

I have a macro to filter 2 different criteria which is "0" and "1" but I need a msg box pop out and stop the macro if there is no criteria found in the auto filter.

Can you advice how I can do that for below coding?

Thanks.

Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$A$8").AutoFilter Field:=1, Criteria1:="=0", _
    Operator:=xlOr, Criteria2:="=1"
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C3").Select
ActiveSheet.Paste

Upvotes: 0

Views: 868

Answers (1)

Marcucciboy2
Marcucciboy2

Reputation: 3263

This should warn you if your filter returns no results. Note that I also had it turn off the filter at the end because some of the results (from C3 and below) could be hidden by the filter.

Sub CheckFilter()

    ''Remove any existing filters
    ActiveSheet.AutoFilterMode = False

    With Range("A1")

        ''get range before filter
        Dim rngBefore As Range
        Set rngBefore = Range(.offset(1, 0), .offset(1, 0).End(xlDown))

        ''filter
        .AutoFilter Field:=1, Criteria1:="=0", Operator:=xlOr, Criteria2:="=1"

        ''get range after filter
        On Error Resume Next
        Dim rngAfter As Range
        Set rngAfter = rngBefore.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0

        ''check whether any cells matching criteria were found
        If rngAfter Is Nothing Then
            MsgBox "No cells match filter"
            Exit Sub
        Else
            rngAfter.Copy Range("C3")
        End If

    End With

    ''Remove filter
    ActiveSheet.AutoFilterMode = False

End Sub

Upvotes: 1

Related Questions