0

I'm trying to filter multiple filters based on user input from a listbox on the same range. However, for some reason the autosort method only sort the target range using the last criteria from testing.

I have searched everywhere, tested out array solutions (reading list info into an array), writing a range of values for filters on worksheets, changing variable type/operator to no avail. Nothing works.

Thanks for your time for reading this, would appreciate it if someone could help me with this.

The user input would look like this

   dim lifecycle as range
   dim List2String as string

        Set lifeCycle = defineColRange(startWs, "Lifecycle Comments (Saks)", True, False)

        For i = 0 To ListBox2_Lifecomments.ListCount - 1
            'looping though the listbox2 to retrieve values
            List2String = ListBox2_Lifecomments.List(i)
            startWs.UsedRange.AutoFilter Field:=lifeCycle.Column, Criteria1:="<>" & List2String
        Next i

        startWs.UsedRange.SpecialCells(xlCellTypeVisible).Interior.Color = rgbLightPink 'testing to see if filter works
Community
  • 1
  • 1
Middle
  • 143
  • 1
  • 8
  • 1
    You should put all the filters in an array and THEN apply the autofilter eg: `Criteria1:<> Array("a", "f", "g", "h")` – xthestreams Jan 15 '18 at 23:12
  • Thanks for the reply, I have tried the array solution, something like {Dim array() as string} Then load array (after declaring appropriate size) with something like looping through {Array(i) = listbox2blah.List(i)}. While applying the filter with following {startWs.UsedRange.AutoFilter Field:=lifeCycle.Column, Criteria1:="<>" & List2String, operator:=xlfiltervalue}, but I would get 1004 error this way. Since I don't know what the user will filter into the listbox on the right, I'm not sure how to get around this without declaring an array with pre-determined values. – Middle Jan 15 '18 at 23:22

1 Answers1

0

After some more digging, and rethinking, essentially I was asking the wrong questions.

The problem statement should be: "How to filter a range NOT equal to multiple criterias?" It is documented here: Explaination of why .autosort doesn't work with not equal to with multiple criterias.

Few Solutions were discussed:

  1. Use another a column to help you determine the result you want to achieve. In my example, it would be looping through all values from user input, and output true/false on another column based on comparison, then filter that helper column to work around this problem.

  2. Filter with an impossible values, this is situational. For example, if you want to filter numbers, set up an outofbound criteria for non-numbers like given in the explanation link above.

  3. Write code to hide the already filtered criteria, and keep filtering what is left with rest of criteria, 2 at a time.

  4. Use advanced filters

Hope this helps others who might get into same problems in the future.

Middle
  • 143
  • 1
  • 8