[Fix]: AutoFilter Method of Range Class Failed (5 Solutions)

If you are struggling to find out the possible causes and solutions of the error message AutoFilter method of Range Class Failed then this article is for you. So, let’s dive into the main article.

(Fixed!) Autofilter Method of Range Class Failed: 5 Solution

Here, we will show 5 possible solutions using the following dataset as a range where we will apply our AutoFilter feature to filter the range based on a criterion.

We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.

Fix-1: Correcting the Field Number to Solve AutoFilter Method of Range Class Failed Issue

Here, we will try to filter the following range based on the sales values greater than 2500 with the help of a VBA code.

For filtering the range we have used the following code

Sub fixing_autofilter_issue_1()

Dim sht As Worksheet
Set sht = Worksheets("Field Number")
sht.Range("B3:D3").AutoFilter field:=100, Criteria1:=">=2500"

End Sub

Here, field: is for indicating the column number of the range, which we have assigned to 100 but there are not more than 3 columns in this range.

So, after pressing F5 we are getting the error message AutoFilter method of Range Class Failed.

➤ To solve this problem, use the correct field number corresponding to the column number of the range (we are using 3 because it is the serial number of the Sales column of the range).

Sub fixing_autofilter_issue_1()

Dim sht As Worksheet
Set sht = Worksheets("Field Number")
sht.Range("B3:D3").AutoFilter field:=3, Criteria1:=">=2500"

End Sub

➤ Press F5.
Now, you will be able to filter your range according to the criteria without having an error message.

Read More: Excel VBA to Check If AutoFilter is On

Fix-2: Using a Correct Range for Solving AutoFilter Method of Range Class Failed Problem

We will try to apply the filter in the following dataset based on the Sales column for values greater than $2,500.00.

We have used the following code for filtering the range of the Range sheet.

Sub fixing_autofilter_issue_2()

Dim sht As Worksheet
Set sht = Worksheets("Range")
sht.Range("D3:D100").AutoFilter field:=3, Criteria1:=">=2500"

End Sub

Here, we can see we have used the range D3:D100 and the field number 3, and these values are not matched up for each other as this range contains only one column.

So, after pressing F5 we are getting the error message again.

➤ Rectify the code into the following one

Sub fixing_autofilter_issue_2()

Dim sht As Worksheet
Set sht = Worksheets("Range")
sht.Range("B3:D3").AutoFilter field:=3, Criteria1:=">=2500"

End Sub

We have changed the range to B3:D3 now.

➤ Press F5.
Afterward, you will be able to filter your range according to the criteria without having an error message.

Fix-3: AutoFilter Method of Range Class Failed Problem Due to Filtering Table

Here, we have the following Table where we will try to apply the filter option with the help of a code to filter this table based on the Sales column.

For this purpose, we have used the following code

Sub fixing_autofilter_issue_3()

Dim sht As Worksheet
Set sht = Worksheets("Table")
sht.Range("B3:D3").AutoFilter field:=3, Criteria1:=">=2500"

End Sub

It seems everything ok in this code. Let’s check it.

But after pressing F5, we are getting the error message.

To resolve this problem we have to convert this table into a range.
➤ Select the table and then go to the Table Design Tab >> Tools Group >> Convert to Range option.

Then, a message box will appear to make sure about the conversion.
➤ Press Yes here.

After that, you will get the range from the created table.

➤ Now, let’s apply the previous code again.

Sub fixing_autofilter_issue_3()

Dim sht As Worksheet
Set sht = Worksheets("Table")
sht.Range("B3:D3").AutoFilter field:=3, Criteria1:=">=2500"

End Sub

➤ Press F5.
As you can see, this time the AutoFilter method worked properly.

Read More: VBA Autofilter: Sort Smallest to Largest

Fix-4: AutoFilter Method of Range Class Failed Problem Due to Filtering Pivot Table

Here, we have the following Pivot Table where we have the sales values in the second column on the basis of which we will apply the AutoFilter method.

So, we have applied the following code.

Sub fixing_autofilter_issue_4()

Dim sht As Worksheet
Set sht = Worksheets("Pivot")
sht.Range("A3:B3").AutoFilter field:=2, Criteria1:=">=2500"

End Sub

Here, field number 2 is for the second column of the range A3:B3.

After pressing F5, you will get the error message AutoFilter method of Range Class Failed.

To solve this problem you can apply this AutoFilter method to the source range of the Pivot table.
As you can see, the source range we have for this table is situated in the Source sheet and here we will apply our code.

➤ Apply the following code for this source range.

Sub fixing_autofilter_issue_4_1()

Dim sht As Worksheet
Set sht = Worksheets("Source")
sht.Range("B3:D3").AutoFilter field:=3, Criteria1:=">=2500"

End Sub

➤ Press F5.
Eventually, you will be able to filter down the range based on the given criteria for the Sales column.

Fix-5: Problem Because of Using the Whole Range Instead of Header

In this section, we are going to discuss a minor problem that hardly evokes the error message AutoFilter Method of Range Class Failed. But you can be aware of the fact that can be the cause of this error.
Like the previous examples, we will apply the AutoFilter method to the following range.

We have used the following code for applying the filter.

Sub fixing_autofilter_issue_5()

Dim sht As Worksheet
Set sht = Worksheets("Header")
sht.Range("B3:D11").AutoFilter field:=3, Criteria1:=">=2500"

End Sub

Here, we have used the range B3:D11 for the whole dataset which is unnecessary, and sometimes it can be the cause of that error message for a large range.

So, we can change that code into the following one by using only the header as a range.

Sub fixing_autofilter_issue_5()

Dim sht As Worksheet
Set sht = Worksheets("Header")
sht.Range("B3:D3").AutoFilter field:=3, Criteria1:=">=2500"

End Sub

After pressing F5, you will get your desired result without facing any issues.

Read More: Excel VBA: Remove AutoFilter If It Exists

Download Workbook

Conclusion

In this article, we tried to cover the possible fixes for the AutoFilter method of the Range Class Failed problem in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Use Custom Autofilter in Excel for More Than 2 Criteria
• VBA to AutoFilter with Multiple Criteria on Same Field in Excel
• How to Autofilter Values Not Equal to a Certain Value with VBA in Excel