none
custom filter RRS feed

  • Question

  • Morning all. Last year, I'd come seeking a multiple filter to focus on the specific contents of cells, using a specific format.

    https://social.msdn.microsoft.com/Forums/en-US/2e738c1e-ca46-4511-8d93-4ba870468aea/multifilter?forum=exceldev

    Hans provided a sample, which I then modified to make what I'd wanted at the time. I'm finding that I now want something akin to the standard text filter, but also with the multiple inputs I'd had before.

    Using the recorder, I created a filter macro which uses the "does not contain" option, and it came up with

    ActiveSheet.Range("$A$1:$G$82").AutoFilter Field:=2, Criteria1:="<>*5410*" _
            , Operator:=xlAnd


    As the filter macro I have uses the same operation, but for the criteria uses an array, obtained from the user's input, I wanted to do something similar.

    So, my code changes the criteria to be as follows:

    Criteria1:="<>*" & arr & "*"

    Apparently this does not work.

    I.e.,

    I want to look for the cells which do not contain my inputs. This should leave me with all cells that do not have within their contents the inputs I provide. In the case of my recorded macro, the criteria excludes 5410.

    what am I doing wrong here? Or can I do this at all? While it's been a while, I remember in 2010, and earlier, we input the ampersand to add elements to the operation.

    I'm now using Excel 2013, and last year I was at another firm, and we were using 2010.

    TYIA.

    Wednesday, June 22, 2016 3:58 PM

Answers

  • Hi SteveDB1,

    I can understand that you are not able to match "not equal to" records in the sheet.

    you had said that that , "If MS ever does make such a change in the code, I'll use it lickety-split.  "

    currently Microsoft will not going to make a change in the syntax .

    but its your feedback and its always valuable.

    so I would like to suggest you to submit it to Excel User Voice.

    If they get the enough feedback regarding this issue then they can think about it and maybe they can add some new feature or functionality separately that can able to do this thing.

    please visit the link below to submit your feedback.

    Excel User Voice

    Till then you can use a work around suggested by Hans Vogelaar MVP to loop through all the records.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by SteveDB1 Friday, June 24, 2016 3:38 PM
    Friday, June 24, 2016 6:46 AM
    Moderator

All replies

  • Using array of values only works to specify a list of fixed values that should be included. AutoFilter does not provide a way to specify an array with wildcard values, nor to specify a list of values to exclude.

    I think you'll have to look for different (preferably) simpler ways of filtering.

    Or loop through the rows and hide the rows that don't meet your criteria.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Clemens Schotte Thursday, June 23, 2016 8:28 AM
    • Unproposed as answer by SteveDB1 Thursday, June 23, 2016 4:30 PM
    Wednesday, June 22, 2016 9:30 PM
  • Using array of values only works to specify a list of fixed values that should be included. AutoFilter does not provide a way to specify an array with wildcard values, nor to specify a list of values to exclude.

    I think you'll have to look for different (preferably) simpler ways of filtering.

    Or loop through the rows and hide the rows that don't meet your criteria.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Hi Hans.

    This is what I'm finding..... I finally decided to just input the hard-values for my macro-- as it shows above, but with a second criteria. It did what I'd wanted, just not the way I'd hoped for.

    Wednesday, June 22, 2016 9:44 PM
  • Hi SteveDB1,

    you had mentioned that, "but with a second criteria. It did what I'd wanted, just not the way I'd hoped for."

    what do you mean by that.

    can you share some snapshot of that?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 23, 2016 2:57 AM
    Moderator
  • Hi Deepak.

    My code is as follows:

    ActiveSheet.Range("$A$1:$G$82").AutoFilter Field:=2, Criteria1:="<>*myfirstvalue*", Criteria2:="<>*mysecondvalue*" _
            , Operator:=xlAnd

    Thursday, June 23, 2016 3:34 AM
  • Hi SteveDB1,

    its looks like same as you posted in original question but it have 2 criteria's.

    so what is your expected behavior from this line of code?

    did you try as suggested by hans to loop the all the rows and hide that doesn't match with your entered criteria.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 23, 2016 5:45 AM
    Moderator
  • Good morning Deepak.

    that takes longer than I wanted to deal with.

    My hope was to use the modification of Hans' original macro from last October-- the link in my first post. As that won't work, I used an easy solution for now. When the need increases at some future point, I'll deal with it then.

    Quite frankly, I'm disappointed that I'm not able to do what I'd described.

    ActiveSheet.Range("$A$1:$G$82").AutoFilter Field:=2, _

    Criteria1:="<>*" & arr & "*", Operator:=xlAnd

    This would've been a whole lot simpler to use. If MS ever does make such a change in the code, I'll use it lickety-split.

    If however you have a better solution, please show me.

    Have a great day.

    Thursday, June 23, 2016 2:54 PM
  • Hi SteveDB1,

    I can understand that you are not able to match "not equal to" records in the sheet.

    you had said that that , "If MS ever does make such a change in the code, I'll use it lickety-split.  "

    currently Microsoft will not going to make a change in the syntax .

    but its your feedback and its always valuable.

    so I would like to suggest you to submit it to Excel User Voice.

    If they get the enough feedback regarding this issue then they can think about it and maybe they can add some new feature or functionality separately that can able to do this thing.

    please visit the link below to submit your feedback.

    Excel User Voice

    Till then you can use a work around suggested by Hans Vogelaar MVP to loop through all the records.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by SteveDB1 Friday, June 24, 2016 3:38 PM
    Friday, June 24, 2016 6:46 AM
    Moderator
  • Great idea Deepak. I did just that. It's now posted on the Excel User Voice page.

    So, while I do not have the answer here I ultimately want, I'll consider your post here the answer, because it gives me a means to post my need to MS, and get a viable solution at some point in what I hope is the not too distant future.

    Friday, June 24, 2016 3:38 PM