• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using UserForm to AutoFilter excluding multiple values

Chihiro

Excel Ninja
Thread below got me thinking today.
http://chandoo.org/forum/threads/how-to-apply-multiple-‘does-not-equal-to’-filter-in-same-column.30476/

As mentioned in the thread, there's no direct way using Autofilter to exclude more than 2 values. And while putting values to be displayed in array works, it's cumbersome when working with large data.

Attached is sample using UserForm and listbox to quickly select values you want to exclude from filter result.

It's not optimized yet, but does what I originally set out to do.

1. Populate listbox with unique values from column on a sheet (currently Sheet1, column A) which contains duplicate values

2. Sort unique values alphabetically without altering worksheet

3. Use 2 listbox to set filter inclusion and exclusion list (Items listed in left box will be displayed, items in right box will be filtered out)

Reference:
For sort method using ArrayList from .Net: see post by jindon
http://www.ozgrid.com/forum/showthread.php?t=181769
 

Attachments

NARAYANK991

Excel Ninja
Hi Chihiro ,

Do you think it would help to include a checkbox for toggling between INCLUDE and EXCLUDE ?

If we have already entered a list of items to be used as the INCLUDE criteria , then just toggling the checkbox can allow the same items to be used as the EXCLUDE criteria.

Narayan
 

chirayu

Well-Known Member
You might want to add some debugs for error messages. E.g. If I add all the names to Exclude & apply filter I get "Run-time error 9: Subscript out of Range".

Also noticed that even if all items selected & visible in filter, filter shows as filtered. So maybe add a clear filters argument if all are in include, rather than the filter argument. Only happens if you exclude some criteria & apply filter, then add them all back into include & apply
 

chirayu

Well-Known Member
If you load the Userform & click "Swap" straight away you get a "Run time error: 13". So you need to add a messagebox for that. - EDIT: Just noticed that if you use the ">>" button and then click "Swap", it'll work as many times as you click it. Odd.

I was also thinking maybe turn it into a multi select listbox. Otherwise you have to manually select all the ones you want to exclude/include & move to other side 1 by 1.
 

NARAYANK991

Excel Ninja
Hi Chirayu ,

I am able to select multiple entries by keeping the CTRL or the SHIFT key pressed and making my selections. Isn't this the same as a multi-select listbox ?

Narayan
 

NARAYANK991

Excel Ninja
Hi Chihiro ,

When I transfer all the entries to the right pane , so that not a single item is included , and click the Apply Filter button , the message which appears needs to be corrected.

Narayan
 

Chihiro

Excel Ninja
@chirayu
Thanks for catching the error. I must have not checked clicking swap upon initializing form. Added IF statement to check for ".ListCount > 0"

Narayan is correct, both list box are already set to fmMultiSelectExtended.

@NARAYANK991
Right, thanks for catching that.

v2.1 with above correction implemented.

Also made slight modification to >> button code.

I'll probably work on making it more dynamic (i.e. let user pick column without modifying code). If I don't think of something else.
 

Attachments

shrivallabha

Excel Ninja
How will it be if we remove the "Apply Filter" button at the bottom and add calling routine to the 4 buttons? It will make it more dynamic.
 

shrivallabha

Excel Ninja
In my personal opinion it isn't a good idea to have a macro that will run each time you make a change
Will not agree to this entirely. It is subjective. One example is worksheet event such as selectionchange which fires at every change of cell selection. Each has its own advantages and disadvantages.

In above case, the user has option of multi-selecting and pushing the criteria elements. Once this is done you need to press "Apply Filter". Since the control is already established, the "Apply Filter" is just one more press of button.

It of course will be significant if there are lot of formulas as that might add to re-calculation load due to firing of an event.

Hope that clears what I was thinking.
 
Top