Autofilter method of range failed error

[littleclayjar]

When a value on a dropdown box is selected, it goes to the three worksheets and filters by that value. However, I keep getting this error "Autofilter method of range failed error" when it gets to the second criteria that filters by field:=4 (see bolded). Am I not deselecting something when I'm supposed to? Thanks for any help you can provide!

' Manager_Filter Macro
'
'
If Worksheets("Instructions").Cells(7, 7).Value = "Enoiu" Then
Worksheets("1a. MOS_US").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("A1:AB1").AutoFilter Field:=6, Criteria1:="Enoiu"
Range("A1:AB1").AutoFilter Field:=4, Criteria1:="<>" & 0

Worksheets("1b. MOS_UK").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("A1:AB1").AutoFilter Field:=6, Criteria1:="Enoiu"
Range("A1:AB1").AutoFilter Field:=4, Criteria1:="<>" & 0

Worksheets("2. JS_ALL").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("A1:R1").AutoFilter Field:=8, Criteria1:="Enoiu"
Range("A1:R1").AutoFilter Field:=5, Criteria1:="<>" & 0

ElseIf Worksheets("Instructions").Cells(7, 7).Value = "Zhou" Then
Worksheets("1a. MOS_US").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("A1:AB1").AutoFilter Field:=6, Criteria1:="Zhou"
Range("A1:AB1").AutoFilter Field:=4, Criteria1:="<>" & 0

Worksheets("1b. MOS_UK").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("A1:AB1").AutoFilter Field:=6, Criteria1:="Zhou"
Range("A1:AB1").AutoFilter Field:=4, Criteria1:="<>" & 0

Worksheets("2. JS_ALL").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("A1:R1").AutoFilter Field:=8, Criteria1:="Zhou"
Range("A1:R1").AutoFilter Field:=5, Criteria1:="<>" & 0

End If

Worksheets("Instructions").Select

End Sub

 

[Gavona]

If you have a single cell selected when you apply autofilter then Excel guesses the range to filter (normally the area around your current cell surrounded by blanks).
If you specify the range as you have done then Excel applies the filter to that range.
If the range is a single row then Excel appears to try to guess which rows but gets confused.

Safest way is to explicitly specify the range. If your data table is surrounded by blanks and contains no totally empty rows or columns then Range("A1").CurrentRegion will work.

Gavin

 

[SkipVought]

Also, as a matter of best and acceptable practices, any table must be CONTIGUOUS -- meaning that there must be no empty row or column in a table.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}