Forum Discussion

Kenneth Nelson's avatar
Kenneth Nelson
Copper Contributor
Jun 01, 2018

Clear Filter with a button

I am only a "medium" excel guy, please be patient....

 

I have added a button to a spreadsheet that has columns that can be filtered.  I have assigned the "clear" function/macro to the button.  However, if no columns are filtered at the time that the button is clicked on, it returns an error.  On the quick access menu, that function is "grayed out" if no columns are filtered to prevent the user from creating the error.  Can I gray out a button???  

Macros & VBA
Need Help

2 Replies

  • Garys Student's avatar
    Garys Student
    Copper Contributor
    Jun 10, 2018

    You can make the macro smart enough to look for the presence of a filter.  If you want to show all data then:

     

    Sub DisplayEverything()
       If ActiveSheet.AutoFilterMode Then
          ActiveSheet.ShowAllData
       End If
    End Sub

     

    If you want to remove filtering completely then:

     

    Sub RemoveTheFilter()
       If ActiveSheet.AutoFilterMode Then
          Cells.AutoFilter
       End If
    End Sub

     

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 01, 2018

    Kenneth-

     

    Do you have the code snippet you're using?  You probably just need to add in some Error Handling or incorporate this small portion of code at the top of the procedure that suppresses errors (Note: this statement should be used with caution):

     

    On Error Resume Next

    Here is some more information on Error Handling by Chip Pearson:

     

    http://www.cpearson.com/excel/errorhandling.htm

     

Resources