none
VBA check if any rows are returned after filter RRS feed

  • Question

  • Hi All,

    I am applying filter using vba and i want to check if there are any rows available after the filter.

    With activesheet.Rows("4:4")
      .AutoFilter 3, Criteria1:="USA", Operator:=xlAnd
      .AutoFilter 4, "NY", xlFilterValues
      End With
    
    On Error Resume Next
    Row = activesheet.Range("F5:K10").Rows.SpecialCells(xlCellTypeVisible).Rows.Count
    If Row > 0 Then
    CALL SOME PROCEDURE
    END IF
    END SUB

    The above code filters properly but there are nearly 7 rows available [but not in range("F5:K10")] and still row shows 0

    Thanks,

    Zav

    Monday, April 11, 2016 5:53 PM

Answers

  •     Dim lRow As Long

        With ActiveSheet.Rows("4:4")
            .AutoFilter 3, Criteria1:="USA", Operator:=xlAnd
            .AutoFilter 4, "NY", xlFilterValues
        End With
        
         lRow = Cells(Rows.Count, "C").End(xlUp).Row
        
        If lRow > 4 Then  'Assumes that row 4 is where your headers are located
            MsgBox "OK, continuing on."
            SomeProcedure
        Else
            MsgBox "No rows are showing."
            Exit Sub
        End If


    Monday, April 11, 2016 6:37 PM

All replies

  •     Dim lRow As Long

        With ActiveSheet.Rows("4:4")
            .AutoFilter 3, Criteria1:="USA", Operator:=xlAnd
            .AutoFilter 4, "NY", xlFilterValues
        End With
        
         lRow = Cells(Rows.Count, "C").End(xlUp).Row
        
        If lRow > 4 Then  'Assumes that row 4 is where your headers are located
            MsgBox "OK, continuing on."
            SomeProcedure
        Else
            MsgBox "No rows are showing."
            Exit Sub
        End If


    Monday, April 11, 2016 6:37 PM
  • Code works great.

    Thanks,

    Zav

    Monday, April 11, 2016 8:00 PM