AutoFilter method of Range class failed

miinstrel

New Member
Joined
Aug 26, 2010
Messages
21
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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:

VBA Code:
    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:

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

miinstrel

New Member
Joined
Aug 26, 2010
Messages
21
If you want to filter a range between dates, try this:

VBA Code:
    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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
you can put your full macro here.
and in which line is the error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

miinstrel

New Member
Joined
Aug 26, 2010
Messages
21
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.

1596755930173.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,697
Messages
5,637,867
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top