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

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.

oldjay wrote:
>
> 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

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

--
Regards,

OssieMac


"oldjay" wrote:

> 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

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


--
Cheers,
Ryan


"oldjay" wrote:

> 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

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

--
Cheers,
Ryan


"OssieMac" wrote:

> 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
>
> --
> Regards,
>
> OssieMac
>
>
> "oldjay" wrote:
>
> > 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

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

--
Regards,

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.



--
Regards,

OssieMac


"Ryan H" wrote:

> 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
>
> --
> Cheers,
> Ryan
>
>
> "OssieMac" wrote:
>
> > 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
> >
> > --
> > Regards,
> >
> > OssieMac
> >
> >
> > "oldjay" wrote:
> >
> > > 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

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.

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.

--
Regards,

OssieMac