Help Needed - VBA Advanced Filter

oscarpompyang

New Member
Joined
Jun 25, 2014
Messages
6
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,581
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Jun 25, 2014
Messages
6
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
ItemClass 1Class 2Class 3Type 1Type 2Type 3
Inventory Range
ItemClass 1Class 2Class 3Type 1Type 2Type 3
Item 1XXXX
Item 2XXX
Item 3XXXX
Item 4XX
Item 5XXXX

<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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,581
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Jun 25, 2014
Messages
6
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,581
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Jun 25, 2014
Messages
6
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.
 

oscarpompyang

New Member
Joined
Jun 25, 2014
Messages
6
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:

TitleGenreBooktype
Book 1Sci-FiPaperback
Book 2RomanceHardbound
Book 3HorrorEbook
Book 4RomanceEbook
Book 5Sci-FiHardbound

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

TitleSci-FiRomanceHorrorPaperbackHardboundEbook
Book 1XXXX
Book 2XXXX
Book 3XXXX
Book 4XX
Book 5XX

<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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,581
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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?
 

Forum statistics

Threads
1,085,854
Messages
5,386,366
Members
401,996
Latest member
mg07p929

Some videos you may like

This Week's Hot Topics

Top