Check all autofilter

[288enzo]

I've been making a lot of headway and am quite proud of myself with VBA (insert smiley face here). I have a dropdown, and based on the selection will autofilter a few different tables. What I'm trying to do now is basically unfilter by checking all options if "all" is in B1.

This is what I have so far, but ActiveSheet.ShowAllData is coming back with a 1004 run-time error. ShowAllData method of Worksheet class failed.

I'm running with Office 2016, sure wish our company would spring for 365. I saw 365 gets a filter function, so jealous.

Sub filtermultiple()

With Worksheets("Sheet3").Range("A4")
.AutoFilter Field:=3, Criteria1:=Cells(1, 2).Value
.AutoFilter Field:=4, Criteria1:="New York"
End With
With Worksheets("Sheet3").Range("A2507")
.AutoFilter Field:=3, Criteria1:=Cells(1, 2).Value
.AutoFilter Field:=4, Criteria1:="California"
End With
With Worksheets("Sheet3").Range("A5010")
.AutoFilter Field:=3, Criteria1:=Cells(1, 2).Value
.AutoFilter Field:=4, Criteria1:="Texas"
End With
If Range("B1").Value = "All" Then
    ActiveSheet.ShowAllData
End If

End Sub

 

[288enzo]

Figured a way to accomplish it.

Sub filtermultiple()

With Worksheets("Sheet3").Range("A4")
.AutoFilter Field:=3, Criteria1:=Cells(1, 2).Value
.AutoFilter Field:=4, Criteria1:="New York"
End With
With Worksheets("Sheet3").Range("A2507")
.AutoFilter Field:=3, Criteria1:=Cells(1, 2).Value
.AutoFilter Field:=4, Criteria1:="California"
End With
With Worksheets("Sheet3").Range("A5010")
.AutoFilter Field:=3, Criteria1:=Cells(1, 2).Value
.AutoFilter Field:=4, Criteria1:="Texas"
End With

If Range("B1").Value = "All" Then
    ActiveSheet.Range("A4").AutoFilter Field:=3
    ActiveSheet.Range("A4").AutoFilter Field:=4
    ActiveSheet.Range("A2507").AutoFilter Field:=3
    ActiveSheet.Range("A2507").AutoFilter Field:=4
    ActiveSheet.Range("A5010").AutoFilter Field:=3
    ActiveSheet.Range("A5010").AutoFilter Field:=4
End If

End Sub