Forum Discussion
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???
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 SubIf you want to remove filtering completely then:
Sub RemoveTheFilter()
If ActiveSheet.AutoFilterMode Then
Cells.AutoFilter
End If
End SubKenneth-
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:
