ActiveSheet.ShowAllData

[oldjay]

This fails at the last line "ShowAllData method of worksheet class failed" if
there is no filtering active

Private Sub CommandButton1_Click() 'Clears all filters
Range("A3").Select
ActiveSheet.ShowAllData
End Sub

End Sub

 

[Dave Peterson]

This is a commandbutton placed on a worksheet?

If yes:

with me
if .filtermode then
.showalldata
end if
end with

If it's a commandbutton on a userform, then using Activesheet makes sense.

 

[OssieMac]

Hi oldjay,

This firstly tests if filtering dropdown arrows are on then tests if a
filter is actually applied.

Private Sub CommandButton1_Click() 'Clears all filters
With ActiveSheet
If .AutoFilterMode Then
If .FilterMode Then
ActiveSheet.ShowAllData
End If
End If
End With
End Sub

 

[Ryan H]

You are getting the error, because there is no data being filtered. In other
words, although AutoFilter Mode is on all rows are visible. I don't normally
use the On Error Resume Next, but this case it may make sense to use it.

By the way, you don't have to select a sheet to run your code. Just
reference your sheet by name. Put the sheet name in where I have Your Sheet
Name Here or just use the code below (which is yours).

Hope this helps! If so, let me know, click "YES" below.

Private Sub CommandButton1_Click() 'Clears all filters
On Error Resume Next
Sheets("Your Sheet Name Here").ShowAllData
On Error GoTo 0
End Sub



Private Sub CommandButton1_Click() 'Clears all filters
Range("A3").Select
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
End Sub

 

[Ryan H]

I figured there was a way to test if data is filtered or not. I hate using
the On Error Goto Next statement.

Private Sub CommandButton1_Click() 'Clears all filters
With Sheets("Your Sheet Name Here")
If .AutoFilterMode And .FilterMode Then .ShowAllData
End With
End Sub

 

[OssieMac]

That should have been just .ShowAllData. Don't need to repeat ActiveSheet in
the With / End With.

Private Sub CommandButton1_Click() 'Clears all filters
With ActiveSheet
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
End Sub

 

[OssieMac]

Thanks for your contribution Ryan. You just forced me into testing this
because for some reason I had the idea that AutoFilterMode and FilterMode
could not be used in the one line because I was sure that FilterMode produced
an error if the filter dropdowns were not visible. However, my belief was not
correct and I have now realized that the code can be simplified even more by
just testing for FilterMode.

Private Sub CommandButton1_Click() 'Clears all filters
With ActiveSheet
If .FilterMode Then .ShowAllData
End With
End Sub

Tested in xl2002 and xl2007 and it works fine.

 

[Slim Slender]

All of these procedures remove filtering completely.
Is there a way to unfilter any particular column but leave the filter
controls at the top for future use.

 

[OssieMac]

Hi Slim,

Following shows how to test if individual filter is on and how to turn off
an individual filter. (You don't have to test if on before turning off; that
is just part of my example. However, need to test for .AutoFilterMode
otherwise the code errors if AutoFilter NOTon.).


Sub FilterTest()

With Worksheets("Sheet1")
If .AutoFilterMode Then
'Test if filter 3 is applied
If .AutoFilter.Filters(3).On Then
'Turn off filter 3
.AutoFilter.Range.AutoFilter Field:=3
End If
End If
End With
End Sub


One would think that .AutoFilter.Filters(3).On = False should work but it
doesn't and I can't find any other method other than my example to turn if
off.