Unfilter a sheet which is filtered
 

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.

Unfilter a sheet which is filtered
 

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

--
jb


"Marty" wrote:

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.

Unfilter a sheet which is filtered
 

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

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.

Unfilter a sheet which is filtered
 

Worked like a charm. Thanks much.

"OssieMac" wrote:


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

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.

Unfilter a sheet which is filtered
 

On Thursday, February 25, 2010 at 11:36:07 AM UTC-8, Marty wrote:
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.

I also need to insure that all rows are showing without deleting the filter buttons, but am fairly novice with G sheets, and I don't understand the programming steps in these answers. Is there a tutorial or guide for how to employ these steps? It looks like an equation to insert somewhere.

Unfilter a sheet which is filtered
 

On Thursday, February 25, 2010 at 11:36:07 AM UTC-8, Marty wrote:
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.

I also need to insure that all rows are showing without deleting the
filter buttons, but am fairly novice with G sheets, and I don't
understand the programming steps in these answers. Is there a
tutorial or guide for how to employ these steps? It looks like an
equation to insert somewhere.

Best way to learn AutoFilter behavior is to do it manually while macro
recorder is on. Be sure to enter something descriptive about your
action in the Description box.

Otherwise...

MyRange.AutoFilter
toggles the dropdown arrow on/off.
any hidden rows are unhidden if in FilterMode

MyRange.AutoFilter 1
unhides any rows currently hidden
leaves the dropdown visible

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Unfilter a sheet which is filtered
 

"...but am fairly novice with G sheets..."

What's a G sheet?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Unfilter a sheet which is filtered
 

"...but am fairly novice with G sheets..."

What's a G sheet?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion