Toggle filter on single column in VBA

[tomleitch]

Hi,

I have a button on my spreadsheet that I want to make toggle a filter in column AP...


So when clicked once I want it to filter everything but blanks in that column

When it is clicked again I want it to clear that filter for just that column.


I'm sure it's pretty straightforward, but I can't seem to work it out!


Any help much appreciated.

Thanks
T

 

[Yongle]

Give this a try:
- applies to active-x command button placed on the worksheet
- amend to match your range and command button

goes in sheet module

Private Sub CommandButton1_Click()
    Const col = "AP"
    Dim r As Range:         Set r = Me.[COLOR=#ff0000]Range("A:AZ")[/COLOR]
    Dim cb As Object:       Set cb = [COLOR=#ff0000]CommandButton1[/COLOR]
    Dim fld As Long:        fld = Columns(col).Column

'toggle
    On Error Resume Next: Me.ShowAllData
    r.AutoFilter
    Select Case cb.Caption = "Blanks"
        Case True
            cb.Caption = "All":         r.AutoFilter Field:=fld
        Case False
            cb.Caption = "Blanks":      r.AutoFilter Field:=fld, Criteria1:="<>", Operator:=xlAnd
    End Select
End Sub

 

[tomleitch]

Yep that's done the business... Cheers Yongle