AutoFilter method of Range class failed

[miinstrel]

Hello gurus,

I've been using the same macro to sort a spreadsheet for years... today it decided to present me with an error for some reason.

"Run-time error '1004':
AutoFilter method of Range class failed

Here's the complete macro (created with the "Record Macro" button in Developer menu). The bold/red section is what's highlighted in the VB debugger:
Sub Auto_Sort()
'
' Auto_Sort Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=4
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=6
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=7
Range("G2").Select
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"F2:F4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"G2:G4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"D2:D4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=4, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/15/2020")
ActiveWindow.SmallScroll Down:=-6
Range("E3").Select
End Sub

It's supposed to filter to only show all rows with a date between 1/1/20 and 12/31/20.
The other sorting macro I use on this spreadsheet still works perfectly. Appreciate any wisdom you might be able to share on how I fix this. Thank you!

 

[DanteAmor]

ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=4, Operator:= _ xlFilterValues, Criteria2:=Array(0, "12/15/2020")
That line only filters values equal to 0 or dated Dec 15.
And it could be like this:

    ActiveSheet.Range("$A$1:$N$4619").AutoFilter Field:=4, Criteria1:=Array("0") _
        , Operator:=xlFilterValues, Criteria2:=Array(0, "12/15/2020")

It's supposed to filter to only show all rows with a date between 1/1/20 and 12/31/20.

If you want to filter a range between dates, try this:

    ActiveSheet.Range("$A$1:$N$4619").AutoFilter Field:=4, Criteria1:= _
        ">=01/01/2020", Operator:=xlAnd, Criteria2:="<=12/31/2020"

 

[miinstrel]

If you want to filter a range between dates, try this:

    ActiveSheet.Range("$A$1:$N$4619").AutoFilter Field:=4, Criteria1:= _
        ">=01/01/2020", Operator:=xlAnd, Criteria2:="<=12/31/2020"

Thanks Dante. I made the above edit, and now when I run the macro it opens VB with an error "Compile error: Syntax error"

 

[DanteAmor]

you can put your full macro here.
and in which line is the error.

 

[Fluff]

Are you trying to filter the SOC sheet?
If so is it the active sheet when you run the macro?

 

[miinstrel]

Yes I am on the SOC sheet.

BUT... I figured it out... typed in a"1" instead of an "l" at one point in the code (they look identical on here).
It's working perfectly now, thank you both.
I don't get why it stopped working all of a sudden in the first place, but oh well.

 

[DanteAmor]

typed in a"1" instead of an "l"

Operator:=xlAnd

I guess it was in that parameter.
It helps to copy all the code, that way you avoid those kinds of errors.

Glad we could help & thanks for the feedback

 

[MARK858]

Just to elaborate on what DanteAmor has said if you click on the green icon in the top right of the code window it copies all the code.