Microsoft Excel - Autofilter with multiple criteria using VBA

Asked By Naveen on 11-Jul-11 03:18 PM

Hello

I have written a VBA code which filters the data using autofilter in another sheet based on certain criteria mentioned and gives me the result. But I would like to know the code to be written incase there's a multiple search criteria like-

Name:

ID:

Region:

Country:

etc

These are the search criteria, if I enter a name like 'John', my code will filter the data (which is in another sheet) and gives me the all the data which contains the name 'John'.

I need a code when there are mutiple search criteria like, if the name is "John' and Region is 'EMEA', the result I want to be of data which matches name as John and region as EMEA and display the result. In other words the VBA code should filter only the criteria entered and fetch the result. The search criteria is much more than mentioned above.Sometimes the search criteria may be two and may be more than two.

pete rainbow replied to Naveen on 11-Jul-11 04:13 PM

you may want to have a look at regular expressions a module ref can be added which gives a bunch of useful search and replace functions

this article is worth reading, although it uses a tiny bit of vba http://www.pither.com/articles/2010/01/31/regex-search-and-replace-in-excel

it does then give you access to the full power of regular expressions which is a wonderfully powerful matching 'language'

and many example os it's usage can be found starting with wiki http://en.wikipedia.org/wiki/Regular_expression

Pichart Y. replied to Naveen on 11-Jul-11 09:57 PM

After macro recorder, then a little bit adjusted, the code below... try it...

Sub AdvanceFilter()

    Sheets("Source").Range("B5:E25").AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=Sheets("Display").Range("A1:B2"), CopyToRange:=Sheets("Display").Range("A4:D4"), Unique:=False

    Sheets("Display").Select
    Range("a2").Select

End Sub

Attachment for you ---> FilterBySelectionManyCriteria.zip

TSN ... replied to Naveen on 11-Jul-11 11:55 PM

Hi..

Look at the below Lnk provides the examples to implement Multiple criteria Filtering ..

http://www.ozgrid.com/VBA/autofilter-vba-criteria.htm

Naveen replied to Pichart Y. on 12-Jul-11 09:01 AM

Hi Pichart

With little changes the code works fine, thank you very much for your time and the attachment as well!

thank you all for your suggestions and links, i was able to gather some extra info from it and has solved by prob

Pichart Y. replied to Naveen on 13-Jul-11 03:31 AM

Hi Naveen,

You are welcome, any question you have just post here!!

Pichart Y.

Naveen replied to Naveen on 13-Jul-11 03:28 PM

Hello Pichart,

I have a small clarification wrt to your code-

1) How can I define the range in autofilter if the criteria are at different cells. eg criteria 1 is at cell A1 and the next is at say B4?

2) In my data there's a criteria which is based on person's name. With your code Iam able to pull the details only if the criteria is matching the first name. eg if the name is James Anderson, and if I enter 'James' in criteria, the result is fine, but if I enter 'Anderson', it doesn't pull James Anderson, but it is pulling the other names which start with Anderson.

But if enter the name like *anderson* it pulls all the data, which is fine. Is there a way where I can get all the related data without entering the criteria using asteriks(*)?

request your help on this!

Naveen replied to Pichart Y. on 13-Jul-11 03:29 PM

Hello Pichart,

I have a small clarification wrt to your code-

1) How can I define the range in autofilter if the criteria are at different cells. eg criteria 1 is at cell A1 and the next is at say B4?

But if enter the name like *anderson* it pulls all the data, which is fine. Is there a way where I can get all the related data without entering the criteria using asteriks(*)?

request your help on this

Pichart Y. replied to Naveen on 14-Jul-11 01:17 AM

Hi Naveen,

Your Quest: How can I define the range in autofilter if the criteria are at different cells. eg criteria 1 is at cell A1 and the next is at say B4?

Ans: what I designed last time is Filter by Advance filter, it is limiation of advance filter to identify the Criteria range in consequecial, so you can not do like that. but you problem can be solve with this new one I attached here.

Your Quest: In my data there's a criteria which is based on person's name. With your code Iam able to pull the details only if the criteria is matching the first name. eg if the name is James Anderson, and if I enter 'James' in criteria, the result is fine, but if I enter 'Anderson', it doesn't pull James Anderson, but it is pulling the other names which start with Anderson.

But if enter the name like *anderson* it pulls all the data, which is fine. Is there a way where I can get all the related data without entering the criteria using asteriks(*)?

Ans: I got the point, then I use autofilter now, and reference your criteria (Name) with 1 variable in the code, then attach the asteriks infront and behind with "*" & SelNm & "*" . You can do the same thing with 2 other criterias, just modify it, I know you can do.

Attachment ----> FilterBySelectionAutoFilter2.zip

Hope it help...
Pichart Y.

Naveen replied to Pichart Y. on 14-Jul-11 02:54 PM

Hi Pichart,

Yes, with little modifications Iam able to adopt it as per my requirement to my data. It is working fine!

Thank you again for your valuable time and inputs for the same!!