Does anyone know how to check whether a filter is active
on a worksheet?

I want to ensure that all data is on show before running a
recorded macro. However if I try to "show all data" on a
worksheet where there is no criteria set on a filter I get
an error message.

Any ideas on how to avoid this?

Thanks,

CiaraG

If activesheet.filtermode then
activesheet.showalldata
End if

--
Regards,
Tom Ogilvy

"CiaraG" wrote in message
...
Does anyone know how to check whether a filter is active
on a worksheet?

I want to ensure that all data is on show before running a
recorded macro. However if I try to "show all data" on a
worksheet where there is no criteria set on a filter I get
an error message.

Any ideas on how to avoid this?

Thanks,

CiaraG

Ciara,

Try this:

Sub ShowAllData()

Dim ws As Worksheet
Set ws = ActiveSheet

If ws.AutoFilterMode = True Then
For Each afFilter In ws.AutoFilter.Filters
If afFilter.On = True Then
ws.AutoFilter.Parent.ShowAllData
Exit For
End If
Next
End If

End Sub

--
Charles
www.officezealot.com


"CiaraG" wrote in message
...
Does anyone know how to check whether a filter is active
on a worksheet?

I want to ensure that all data is on show before running a
recorded macro. However if I try to "show all data" on a
worksheet where there is no criteria set on a filter I get
an error message.

Any ideas on how to avoid this?

Thanks,

CiaraG

this should work:

If ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData

chrs, john

"CiaraG" wrote in message
...
Does anyone know how to check whether a filter is active
on a worksheet?

I want to ensure that all data is on show before running a
recorded macro. However if I try to "show all data" on a
worksheet where there is no criteria set on a filter I get
an error message.

Any ideas on how to avoid this?

Thanks,

CiaraG