Unfilter a sheet which is filtered

[Marty]

Hi, all. Need some help, please.

I have several sheets in a workbook that I need the code to look at and
determine if an autofilter is "in use", (i.e., if a filter is applied on any
of the columns of the sheet). If it is, I need the code to unfilter the
sheets. If it is not, I need the code to ignore it.

I've tried variations of "ShowAllData", but I find that when it tries to
execute on a sheet where a filter is not applied, the macro crashes.
Something like this would be helpful:

If [autofilter is in use] then ShowAllData

I don't want to completely get rid of the filters (in other words I want the
filter buttons to stay in place), I just want to show all of the data IF a
filter is actually being used.

Help would be appreciated.

Thanks.

 

[john]

see if this does what you want.

Sub RemoveFilters()

Dim WS As Worksheet

With Application

.EnableEvents = False
.ScreenUpdating = False




For Each WS In ActiveWorkbook.Sheets

On Error Resume Next

WS.ShowAllData

On Error GoTo 0

Next


.EnableEvents = True
.ScreenUpdating = True

End With

End Sub

 

[OssieMac]

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
If ws.FilterMode Then ws.ShowAllData
Next ws


or you can use With/end with like the following

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
With ws
If .FilterMode Then .ShowAllData
End With
Next ws

 

[Marty]

Worked like a charm. Thanks much.

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
If ws.FilterMode Then ws.ShowAllData
Next ws


or you can use With/end with like the following

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
With ws
If .FilterMode Then .ShowAllData
End With
Next ws

--
Regards,

OssieMac

Hi, all. Need some help, please.

I have several sheets in a workbook that I need the code to look at and
determine if an autofilter is "in use", (i.e., if a filter is applied on any
of the columns of the sheet). If it is, I need the code to unfilter the
sheets. If it is not, I need the code to ignore it.

I've tried variations of "ShowAllData", but I find that when it tries to
execute on a sheet where a filter is not applied, the macro crashes.
Something like this would be helpful:

If [autofilter is in use] then ShowAllData

I don't want to completely get rid of the filters (in other words I want the
filter buttons to stay in place), I just want to show all of the data IF a
filter is actually being used.

Help would be appreciated.

Thanks.