none
Excel VBA Macro to stop when no filtered data can be found RRS feed

  • Question

  • Hi all,

    I'm looking for a piece of code that can go after the below to stop my macro running if there are no results from the Filter. It currently works perfectly, except this minor flaw.

        ActiveSheet.Range("$A$6:$N$3006").AutoFilter Field:=5, Criteria1:="<>"
        ActiveSheet.Range("$A$6:$N$3006").AutoFilter Field:=14, Criteria1:="="

    Preferably, I would like a MsgBox to pop-up confirming nothing could be found, the filters to be cleared but still remain on and the sheet to be password protected again.

    I've looked everywhere but cannot seem to adapt any code to fit in to get exactly what I would like.

    Thanks in advance! Dean.

    Friday, June 15, 2012 9:32 AM

Answers

  • Hello DA74,

    There is no real need to specify the actual range of the filtered data. AutoFilter.Range is a dynamic reference for the filtered range. The following code should answer your question.

        Dim rngFiltCol As Range

        With ActiveSheet.AutoFilter.Range
       
            .AutoFilter Field:=5, Criteria1:="<>"
            .AutoFilter Field:=14, Criteria1:="="
            
             Set rngFiltCol = .Resize(.Rows.Count, 1) _
                            .Cells.SpecialCells(xlCellTypeVisible)
       
            If rngFiltCol.Cells.Count = 1 Then      'Only column headers visible
                MsgBox "No data visible; only column headers visible."
                'Following line turns off all filters
                .ShowAllData
               
                'Alternative examples to turn off individual filters
                '.AutoFilter Field:=5
                '.AutoFilter Field:=14
            Else
                MsgBox rngFiltCol.Cells.Count - 1 & " rows of data visible."
            End If
        End With

    Explanation of the code.

    ActiveSheet.AutoFilter.Range  is a dynamic reference to the entire AutoFiltered range. (Includes visible and non visible cells)

    Set rngFiltCol = .Resize(.Rows.Count, 1)    Resizes the range down to a single column.  (Includes visible and non visible cells)

     .Cells.SpecialCells(xlCellTypeVisible)     Includes only visible cells.

    Now you can count CELLS in a range containing both visible and non visible data but you CANNOT COUNT ROWS because the count stops after the first non contiguous row. Therefore if the count of cells in a single column is 1 then only the column headers are visible. If greater than 1 then data is also visible.

    The message box displays the count of visible data less 1 for the column headers.

    Following added with Edit.

    I am not sure what you really mean by "the filters to be cleared but still remain on". The filters only have 2 states; On or Off. I am sssuming that you mean leave AutoFilter on but no actual filters set?


    Regards, OssieMac


    • Edited by OssieMac Friday, June 15, 2012 11:24 AM
    • Marked as answer by DA74 Friday, June 15, 2012 1:35 PM
    Friday, June 15, 2012 11:20 AM

All replies

  • See if below helps.You have not told what if some data found.Also change the password.

    Option Explicit
    Sub MyMacro()
    Dim wsSheet As Worksheet, rRng As Range
    Set wsSheet = ActiveSheet
    wsSheet.Unprotect Password:="111"
    'Do not forget to change the Password
    Set rRng = wsSheet.Range("$A$6:$N$3006")
    With rRng
        .AutoFilter Field:=5, Criteria1:="<>"
        .AutoFilter Field:=14, Criteria1:="="
        If .SpecialCells(xlCellTypeVisible).Address = .Rows(1).Address Then
            MsgBox "Hi, There is no data"
            wsSheet.AutoFilter.ShowAllData
        Else
            MsgBox "Hi, Some issue noiced"
        End If
        
        wsSheet.Protect Password:="111"
                'Do not forget to change the Password
    End With
               
    End Sub

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.

    Friday, June 15, 2012 11:02 AM
    Answerer
  • Hello DA74,

    There is no real need to specify the actual range of the filtered data. AutoFilter.Range is a dynamic reference for the filtered range. The following code should answer your question.

        Dim rngFiltCol As Range

        With ActiveSheet.AutoFilter.Range
       
            .AutoFilter Field:=5, Criteria1:="<>"
            .AutoFilter Field:=14, Criteria1:="="
            
             Set rngFiltCol = .Resize(.Rows.Count, 1) _
                            .Cells.SpecialCells(xlCellTypeVisible)
       
            If rngFiltCol.Cells.Count = 1 Then      'Only column headers visible
                MsgBox "No data visible; only column headers visible."
                'Following line turns off all filters
                .ShowAllData
               
                'Alternative examples to turn off individual filters
                '.AutoFilter Field:=5
                '.AutoFilter Field:=14
            Else
                MsgBox rngFiltCol.Cells.Count - 1 & " rows of data visible."
            End If
        End With

    Explanation of the code.

    ActiveSheet.AutoFilter.Range  is a dynamic reference to the entire AutoFiltered range. (Includes visible and non visible cells)

    Set rngFiltCol = .Resize(.Rows.Count, 1)    Resizes the range down to a single column.  (Includes visible and non visible cells)

     .Cells.SpecialCells(xlCellTypeVisible)     Includes only visible cells.

    Now you can count CELLS in a range containing both visible and non visible data but you CANNOT COUNT ROWS because the count stops after the first non contiguous row. Therefore if the count of cells in a single column is 1 then only the column headers are visible. If greater than 1 then data is also visible.

    The message box displays the count of visible data less 1 for the column headers.

    Following added with Edit.

    I am not sure what you really mean by "the filters to be cleared but still remain on". The filters only have 2 states; On or Off. I am sssuming that you mean leave AutoFilter on but no actual filters set?


    Regards, OssieMac


    • Edited by OssieMac Friday, June 15, 2012 11:24 AM
    • Marked as answer by DA74 Friday, June 15, 2012 1:35 PM
    Friday, June 15, 2012 11:20 AM
  • Excellent. That works great OssieMac. Also many thanks for the explanation of the code.

    To answer your question, I did want the filters on but no filters set.

    Thank you again :)

    Dean.

    Friday, June 15, 2012 1:35 PM