Help Needed - VBA Advanced Filter

[oscarpompyang]

Hi, new user here. After scouring the web for help I decided to ask the experts.

I have a database with tagging for each of the entries, and i've set up an advancedfilter macro on it based on user selections. the criteria selection is set up in such a way that users can select multiple options from each of the criteria before they search. the problem is, i belatedly realized that by selecting multiple options, the advancedfilter will show only items tagged to both options in the database. i need it to show the items that are either <option 1> or <option 2>, etc. Is there a way you can suggest for me to go about doing this? I don't want users limited to just one option per criteria per search...

Thanks in advance!

 

[RoryA]

If you want OR criteria, they must be on separate rows in your criteria range, or you can use a formula as the criteria. If you need more details, then so will we.

 

[oscarpompyang]

Right! So here's a simplified version of the database i had... my explanation below might be a bit overkill in details, but the more details you have the better chances you can help.

User Criteria
Item	Class 1	Class 2	Class 3	Type 1	Type 2	Type 3
Inventory Range
Item	Class 1	Class 2	Class 3	Type 1	Type 2	Type 3
Item 1	X		X	X	X
Item 2		X	X		X
Item 3	X	X		X		X
Item 4			X			X
Item 5	X	X			X	X

<tbody>
</tbody>

The user selection interface is set up with tick boxes for "Class" (Class 1,2,3 being the options) and "Type" (type 1,2,3). Once the user selects what he wants, I transpose the choices to the User Criteria, and I used the below code to apply the advancedfilter.

Sheets("Inventory").Range("InventoryRange").AdvancedFilter Action:=xlFilterInPlace, criteriarange:=Sheets("Inventory").Range("UserCriteria"), unique:=True

As i mentioned, this doesn't work for me... for example, if user selects Class 1 & 2, it will only show Item 3 & 5, because they're the only items tagged to both Classes. I need it to show Items 1, 2, 3 & 5, because those items are EITHER Class 1 OR Class 2. Hope this makes sense!

 

[RoryA]

As I mentioned, you would have to have the X on separate rows in the criteria range. So if we assume the criteria range starts in A1 (with the headers), for Class 1 or Class2 you would need an X in B2 and C3 and you would have to include the three rows in the UserCriteria range. However, you have to make sure that you do not include any blank rows in the UserCriteria range, or you'll just get all results returned. You may find it easier to use a formula. I have a post on this here.

 

[oscarpompyang]

Thanks Rory, this put me on the right track to fixing my code. As a follow-up question though, how should i limit the criteria range in code to avoid including blank rows? the database i am working on has 10 options for each of the criteria. the code that applies the advancedfilter has a fixed criteria range - if i change that range to include the maximum selected choices, i run the risk of blank rows when less choices are selected.

 

[RoryA]

I'd suggest that using formulas for the criteria may well be simpler. If you can't (or don't want to) do that, you'd need to loop through each column of the criteria range, check the highest used row number in each and then resize the criteria range to the maximum of those numbers.

 

[oscarpompyang]

Simpler is always better, all the more so with as many options as the one my database has. I'll try to make sense of how to integrate the formula into my criteria range from your post and see what happens. Appreciate your help - the post you shared turned a bulb on in my head! - and any additional information you may know of to help me out.

 

[RoryA]

Debra's site is always useful: http://www.contextures.com/xladvfilter02.html

 

[oscarpompyang]

Hello again, I'm having trouble proceeding. The problem stems from the fact that all of the example tables I've seen that make use of multiple-row search criteria have one tag per criteria:

Title	Genre	Booktype
Book 1	Sci-Fi	Paperback
Book 2	Romance	Hardbound
Book 3	Horror	Ebook
Book 4	Romance	Ebook
Book 5	Sci-Fi	Hardbound

<tbody>
</tbody>

I understand that the approach that using a formula as criteria range would work on this kind of table, but the data i have is stored in a setup allowing more flexibility in the tagging of each item. I'm having trouble seeing how a formula in the criteria range could be applicable to this kind of setup.

Title	Sci-Fi	Romance	Horror	Paperback	Hardbound	Ebook
Book 1	X	X		X	X
Book 2		X	X		X	X
Book 3	X		X	X		X
Book 4			X		X
Book 5	X					X

<tbody>
</tbody>

What comes to mind now is to allot a separate row in the criteria range for each of the options, which in this example would mean 6 rows. The table i am working with has a total of 26 options. Am i going about this the right way?

 

[RoryA]

I think you'll need a hybrid approach. You can use a regular criterion for the Title field as it's a straight choice, and use formula criteria for the Genre and BookType using an additional table like you have shown there. Do you have a sample file you can put onto a share like OneDrive or Dropbox?