[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.

Download Workbook


5 Fixes for Autofilter Method of Range Class Failed Problem

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.

AutoFilter method of Range class failed

We have used Microsoft Excel 365 version here, you can use any other versions 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.

AutoFilter method of Range class failed

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.

Correcting field number

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

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

Correcting field number

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

AutoFilter method of Range class failed

Read More: Excel VBA to Check If AutoFilter is On (4 Easy Ways)


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.

AutoFilter method of Range class failed

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.

using a correct range

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

AutoFilter method of Range class failed

➤ 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.

using a correct range

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

AutoFilter method of Range class failed


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.

AutoFilter method of Range class failed

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.

filtering Table

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

filtering Table

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.

AutoFilter method of Range class failed

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

filtering Table

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

filtering 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

filtering Table

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

AutoFilter method of Range class failed


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.

AutoFilter method of Range class failed

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.

filtering Pivot Table

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

filtering Pivot Table

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.

filtering Pivot Table

➤ 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

filtering Pivot Table

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

AutoFilter method of Range class failed


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.

AutoFilter method of Range class failed

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.

selecting header as 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

selecting header as range

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

AutoFilter method of Range class failed


Conclusion

In this article, we tried to cover the possible fixes for the AutoFilter method of 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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo