VBA Toggle off/on filter (can't use slicer as file too big)

[Melimob]

Hi

I have a table where I want to either show 'blanks' or show 'all'.

The below code just keeps it on showing blanks. Can anyone help to add a filter if not applied and then show all / just show blanks based on field 5?

many thanks in advance

Private Sub ToggleButton1_Click()


If ActiveSheet.AutoFilterMode = True Then
'autofilter is on...
ActiveSheet.AutoFilterMode = False
Else
'autofilter is not on...
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:=""
End If


End Sub

 

[Yongle]

try this

Private Sub ToggleButton1_Click()
    On Error Resume Next
    Select Case ToggleButton1.Value
        Case True:  Me.ListObjects(1).Range.AutoFilter Field:=5, Criteria1:=""
        Case Else:  Me.ShowAllData
    End Select
End Sub

 

[Melimob]

try this

Private Sub ToggleButton1_Click()
    On Error Resume Next
    Select Case ToggleButton1.Value
        Case True:  Me.ListObjects(1).Range.AutoFilter Field:=5, Criteria1:=""
        Case Else:  Me.ShowAllData
    End Select
End Sub

thank you Yongle however I need to adjust that Me.ListObjects(1).Range.AutoFilter Field:=5, Criteria1:=Show all Data

so that it doesn't clear the other filters?

Do you know how I would do this please?

thank you!

 

[Melimob]

thank you Yongle however I need to adjust that Me.ListObjects(1).Range.AutoFilter Field:=5, Criteria1:=Show all Data

so that it doesn't clear the other filters?

Do you know how I would do this please?

thank you!

Its ok, I figured it out!!

Private Sub ToggleButton1_Click()
    On Error Resume Next
    Select Case ToggleButton1.Value
        Case True:  Me.ListObjects(1).Range.AutoFilter Field:=5, Criteria1:=""
        Case Else:  Me.ListObjects(1).Range.AutoFilter Field:=5
End SelectEnd Sub

thank you!!!

 

[Yongle]

thanks for posting your final solution