Unfilter a sheet which is filtered

M

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.
 
J

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
 
O

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
 
M

Marty

Worked like a charm. Thanks much.

OssieMac said:
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


Marty said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top