Search code examples
vba

Excel VBA to clear filters from table and supress any errors

Go easy im a newbie, and new to Excel and VBA etc.

I have a large database that contains user profile details which is in table form. the issue i am having is i have a command button to clear the filters that are applied to the table, i got the requried macro by recording it, however if there are no filters applied i run into the below error:

Run-time error '1004':

ShowAllData method of worksheet class failed

I know why im getting the error, which is simply because there are no filters applied and hence it throws this error up, what i would like is to have this error ignored entirely.

Here is my Command Button code:

Private Sub CommandButton25_Click()
ClearFilters2 Macro
Range("Table24[#Headers]").Select
ActiveSheet.ShowAllData
       
End Sub

Any help to supress the runtime error would be appreciated, could i ask that the code be supplied with the correction?

Thanks,

JD

Solution

  • You can use On Error Resume Next.

    You should ensure to turn error handling back on at a suitable place in your code On Error GoTo 0.

    You can also create a label and tell the code to go to this label if an error occurs On Error GoTo <label>.

    See Chip Pearson's article on error handling for more information.