[Solved] VBA : Auto filter

WesPowell · Apr 29th 2004

I have a workbook with several sheets, some with AutoFilters others without.

If I want to make changes with a macro, those changes are only made to what is visible, not to what is hiden by the filter.

I tryed this:

Sub SeeFilter()
Dim w As Worksheet
For Each w In Worksheets
If w.FilterMode Then Selection.AutoFilter Field:=1
Next w
End Sub

But it only sets the filter to all on the the sheet where I am when I run the macro, not to all other.

Any ideas to solve this? Thanks,

Wes Powell

WesPowell · Apr 29th 2004

I think I found the solution :biggrin:

Sub SeeFilter()
Dim w As Worksheet
For Each w In Worksheets
If w.FilterMode Then w.Range("a1").AutoFilter field:=1
Next w
End Sub

Thanks

yjoshi · Apr 29th 2004

Ho Wes,

The post is a bit ambiguous!! Not really sure if you want to remove all filters on all sheets or do something special if there is a filter.

Following code will make sure that autofilter for all sheets is set to false, so that your macro can run and will not be affected by the filters.

Code

Sub SeeFilter()
Dim w As Worksheet
For Each w In Worksheets
    w.AutoFilterMode = False
Next w
End Sub

If you post what you are doing in your code, we can suggest some better way!!

WesPowell · Apr 29th 2004

Hi, thanks yjoshi.

What I wanted to do was to show all data if filter was on but leave the filter there. Meanwhile, I found some easier code for that:

Code

Sub SeeFilter() 
Dim w As Worksheet 
For Each w In Worksheets 
       If w.FilterMode Then w.ShowAllData
Next w 
End Sub

Participate now!