toggle between autofilter Criteria1:= "*" and "all"

[Mike Gallagher]

I'm using autofilter to collapse and expand a range based on value of Criteria1
I would like to use code to be able to alternate between Criteria1:="1" and
Criteria1:="All"

This is the code that i thought would work but hasn't -

Dim filtcrit

filtcrit = Criteria1

With ActiveSheet
If filtcrit = 1 Then
Selection.AutoFilter Field:=1

Else
Selection.AutoFilter Field:=1, Criteria1:="1"

End If
End With

 

[sebastienm]

Hi,
try:
''' --------------------------------------

Sub test()
Dim wsh As Worksheet
Set wsh = ActiveSheet
With wsh
If .AutoFilter.Filters(1).On Then
.AutoFilter.Range.AutoFilter Field:=1
Else
.AutoFilter.Range.AutoFilter Field:=1, Criteria1:="=1"
End If
End With
End Sub
''' ---------------------------------------------

 

[Mike Gallagher]

Thank you, that will work well

Hi,
try:
''' --------------------------------------

Sub test()
Dim wsh As Worksheet
Set wsh = ActiveSheet
With wsh
If .AutoFilter.Filters(1).On Then
.AutoFilter.Range.AutoFilter Field:=1
Else
.AutoFilter.Range.AutoFilter Field:=1, Criteria1:="=1"
End If
End With
End Sub
''' ---------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>
<http://www.ready-reports.com>