EXCEL VBA Not exact match auto filter

0
votes

I'm having a question in regards to the AutoFilter in macro. The code is below.

ActiveSheet.Range("$A$1:$F$1048576").AutoFilter Field:=2, Criteria1:=r1

r1 is the text that I want to filter. However, I notice that the filter will look for the exact match based on what I put in r1. For example, I put in r1 = "banana", but the list has only "bananas", it will return 0 matched item. But if I do it manually, go to the filter section, type "banana", it will automatically choose the closet match "bananas" to the one i typed.

May question: Is there any way can still use this code but not the nearly-match result for the above code?

Thanks,

Tony

1
excelvba

1 Answers

0
votes

It is not looking for or choosing the closest match but rather completing your input with a wild card.

You can use it in your criteria :

Sub filter_()

r1 = "banana"
Range("A1:A9").AutoFilter field:=1, Criteria1:=r1 & "*", Operator:=xlFilterValues

End Sub

This will include "banana" & "bananas" in your filtered list.