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

Data Filter

Is there any way we can inverse the selected vales in a data filter? Assume, we have 5 values in Column A and already filtered Values 2 & 4. I am looking for a solution whereby I should be able to select rest of the values like 1, 3 & 5 by click of a button.

Can anyone help with a macro or any alternate solution please?

This looks extremely painful, when there are more values.
 
@bobhc Tables don't let you invert a filter at the click of a button.
@dramnath1981 - I have some code that does this. I'm in the process of making some changes to it, but should have it finished in a couple of days. Email me on weir.jeff@gmail.com if you like and I'll forward it to you for testing.
 
Sample file attached, that lets you invert a filter in a Table or PivotTable, plus filter them based on a whole bunch of crazy wildcard combinations. Very complex code, using English version of Excel 2013. Should run on Excel 2010 or later.

Very beta. Save your work first. Love to hear of any bugs or feedback.
 

Attachments

  • Evil Genius Filter_20150903 v1.xlsm
    277.6 KB · Views: 3
Glad you like it. I have a new friend at Microsoft who's using this too. I told him to pass it on to the Excel Dev team there, with the hope that they say:

Cool. Now why don't we do that?
 
Very true. I often feel handicapped without this feature while filtering values. I believe it will be a blessing for all of us if Microsoft considers your suggestion.
 
Glad you like it. I have a new friend at Microsoft who's using this too. I told him to pass it on to the Excel Dev team there, with the hope that they say:

Cool. Now why don't we do that?

Cool buddy!!! Keep on the climbing...
 
@All - I picked up an oversight in the code relating to non-OLAP pivottables, that resulted in any slicers that referenced the field being filtered getting deleted. Have amended that in the attached code.Bound to be more things that I fix or improve over the next few weeks too.

@Asheesh: Thanks. This is one of the hardest things I've ever done, and I've been working on some of the code on and off for well over a year.

Plenty more where this came from too. Heaps of this stuff going in to my book. Which is why I keep delaying the publication of it!
 
Weird: I can't seem to see the file in the directory when I'm trying to upload it here. I know it's there, but the window that comes up when I click Upload a File doesn't show it in the folder. Same thing happenned yesterday. I'll try a restart, see if that fixes the issue
 
Back
Top