Auto filter on a command button

[Craig1]

Hi All,
I use command buttons quite a lot and like them for the workbooks I use.
I have about 20 command buttons on a new sheet I have set up but I keep getting a problem that I need help with.
The issue I have is all the auto filters work fine using the command button except when I filter a certain range and nothing is in that range that is still "In progress" so the rows stay empty. All this is fine until I come to filter the next range, it looks like that because the screen is empty (below the auto filter line) the next filter won't work. So I need to know if I am missing something. When the screen is populated there are no issues.

A standard auto filter for the command button is below.

Any help would be appreciated.

Private Sub CommandButton5_Click()
On Error GoTo Err_Handler

Selection.AutoFilter Field:=12, Criteria1:="Powder", Operator:=xlAnd
Selection.AutoFilter Field:=13, Criteria1:="In progress", Operator:=xlAnd

Exit Sub
Err_Handler:
If Err.Number = 1004 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
End If
End Sub

Thanks again.
Craig

 

[Peter_SSs]

I don't understand what the problem is. Can you explain it again differently? Be specific with what the actual problem with the current code is.

 

[Craig1]

Thanks for the reply Peter.
The problem is when auto filter is first turned on and nothing is actually filtered the above code works fine. But if I filter another range and find there is nothing in that range, basically the page is empty. So when I try to filter using the code above again it doesn't seem to work and the cells stay blank.


Thanks again.
Craig.

 

[Peter_SSs]

I'm still not certain I understand the problem but try adding this just before the autofilter part of your existing code.

With ActiveSheet
    If .FilterMode Then .ShowAllData
End With

 

[Craig1]

Thanks for that Peter is worked a treat. For some reason it the auto filter wouldn't work until it saw something in the columns, now using the ShowAllData seems to work fine.

Craig

 

[Peter_SSs]

Thanks for that Peter is worked a treat. For some reason it the auto filter wouldn't work until it saw something in the columns, now using the ShowAllData seems to work fine.

Craig

The autofilter was working, but applying an autofilter to a column, as your code was doing, doesn't remove existing filters from other columns.