Filter Check if Criteria is Present Before Attempting

[288enzo]

Hello,

My macro was working quite well, up until when it encounters trying to filter by criteria that doesn't exist.

As an example, the below would already be filtered by field 5, then I would want to apply an additional filter to field 8.

        With ws
            .Rows(2).AutoFilter field:=5, Criteria1:=dlr
            .Range(ws.Cells(1, 13), ws.Cells(1, 22)).Copy
        End With

        With ws
            .Rows(2).AutoFilter field:=14
            .Rows(2).AutoFilter field:=8, Criteria1:="PA"
            .Range(ws.Cells(1, 13), ws.Cells(1, 22)).Copy
        End With

If the criteria "PA" doesn't exist, everything gets thrown out of whack.

Is there something I can do to somehow check if PA exists before trying to filter by field 8?

Thank you

 

[Alex Blakenburg]

How about just applying the filter, testing for no data and if no data found releasing the filter ?

        With ws
            .Rows(2).AutoFilter field:=14
            .Rows(2).AutoFilter field:=8, Criteria1:="PA"
            
            Dim rng As Range
            Set rng = .Range("A1").CurrentRegion
            If rng.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Range(ws.Cells(1, 13), ws.Cells(1, 22)).Copy
            Else
               .Rows(2).AutoFilter field:=8
            End If
        End With

 

[288enzo]

How about just applying the filter, testing for no data and if no data found releasing the filter ?

        With ws
            .Rows(2).AutoFilter field:=14
            .Rows(2).AutoFilter field:=8, Criteria1:="PA"
           
            Dim rng As Range
            Set rng = .Range("A1").CurrentRegion
            If rng.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Range(ws.Cells(1, 13), ws.Cells(1, 22)).Copy
            Else
               .Rows(2).AutoFilter field:=8
            End If
        End With

I made a few small adjustments adding debug.print. I think having my filter on row 2 is throwing this off. I say that because when I run it I get "data exists" when there is no data, meaning "SA" with "No" does not exist.

    Set ws = Sheets("Report")
        
        With ws
            .Rows(2).AutoFilter field:=14, Criteria1:="No"
            .Rows(2).AutoFilter field:=8, Criteria1:="SA"
            
            Dim rng As Range
            Set rng = .Range("A1").CurrentRegion
            If rng.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                Debug.Print "data exists"
            Else
                Debug.Print "data does not exist"
               .Rows(2).AutoFilter field:=14
               .Rows(2).AutoFilter field:=8
            End If
        End With

 

[288enzo]

I made a few small adjustments adding debug.print. I think having my filter on row 2 is throwing this off. I say that because when I run it I get "data exists" when there is no data, meaning "SA" with "No" does not exist.

    Set ws = Sheets("Report")
       
        With ws
            .Rows(2).AutoFilter field:=14, Criteria1:="No"
            .Rows(2).AutoFilter field:=8, Criteria1:="SA"
           
            Dim rng As Range
            Set rng = .Range("A1").CurrentRegion
            If rng.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                Debug.Print "data exists"
            Else
                Debug.Print "data does not exist"
               .Rows(2).AutoFilter field:=14
               .Rows(2).AutoFilter field:=8
            End If
        End With

Figured it out, thanks for the help. I had to change the count to "> 2"

    Set ws = Sheets("Report")
        
        With ws
            .Rows(2).AutoFilter field:=14, Criteria1:="No"
            .Rows(2).AutoFilter field:=8, Criteria1:="SA"
            
            Dim rng As Range
            Set rng = .Range("A1").CurrentRegion
            If rng.Columns(1).SpecialCells(xlCellTypeVisible).Count > 2 Then
                Debug.Print "data exists"
            Else
                Debug.Print "data does not exist"
               .Rows(2).AutoFilter field:=14
               .Rows(2).AutoFilter field:=8
            End If
        End With