Excluding items out of Filter in Macro

[Laura Ford]

I'm looked, but cannot find solution for the below.

I'm using a macro to filter out select suppliers. I'm fine as long as it is one or two. I need to now exclude all but 3.

This works for 2
ActiveSheet.Range(Selection, Selection.End(xlDown)).AutoFilter Field:=1, Criteria1:="<>66", Operator:=xlAnd, Criteria2:="<>79"
Tried this for 3 or more and it does not work.
ActiveSheet.Range(Selection, Selection.End(xlDown)).AutoFilter Field:=1, Criteria1:="<>66", Operator:=xlAnd, Criteria2:="<>79", Operator:=xlAnd, Criteria3:="<>382"

Thank you,

Laura

 

[Jerry Sullivan]

No, I don't agree. My list is numbers 1 to 26 (with 1 repeat). The only difference is whether you want a zero based array or a 1 based array!

The array of numbers has to be cast as an array of strings somewhere in the process before it can be used correctly as a Criteria1 argument for an autofilter with Operator:=xlFilterValues

In my code example in post #2, that happens in two places and it's only necessary in one. If they are both eliminated and not replaced with some other statement to convert the numbers, all values are filtered out by the autofilter.

Your code example in post #4 works correctly because the Filter Function calls return String arrays.

Sub FilterTest()
  Const sExcludes$ = "1,2,3,4,5,6"
  Dim vAll

  
  With ActiveSheet.Range(Selection, Selection.End(xlDown))
    vAll = Application.Transpose(.Resize(, 1).Cells)
  End With

  
  Debug.Print "Data Type of vAll: " & TypeName(vAll)  & " -> Data Type of vAll(1): " & TypeName(vAll(1))

   '-- displays: [COLOR="#0000CD"]Data Type of vAll: Variant() -> Data Type of vAll(1): Double[/COLOR]


  vAll = Filter(vAll, "~", False) '//resize the array & cast as String()

  
  Debug.Print "Data Type of vAll: " & TypeName(vAll)   & " -> Data Type of vAll(1): " & TypeName(vAll(1))

   '-- displays: [COLOR="#0000CD"]Data Type of vAll: String() -> Data Type of vAll(1): String[/COLOR]

End Sub

 

[Jerry Sullivan]

I played around in XL2003 to duplicate this process there and here's what I came up with...

Sub FilterExcludes2()
  Const sExcludes$ = "1,2,3,4,5,6"
  Dim vAll, n&
  
  Application.ScreenUpdating = False
  With ActiveSheet.Range(Selection, Selection.End(xlDown))
    If WorksheetFunction.CountA(.Cells) > 10000 Then
      MsgBox "Range(" & .Address & ") has too many items"
      Exit Sub
    End If
    vAll = Application.Transpose(.Resize(, 1).Cells)
    For n = LBound(vAll) + 1 To UBound(vAll)
      If InStr(1, sExcludes, vAll(n)) > 0 Then Rows(n).Hidden = Not Rows(n).Hidden
    Next 'n
  End With
  Application.ScreenUpdating = True
End Sub

..which toggles the hidden rows. Not sure how efficient it would be with large amounts of items but I doubt this isn't any more an issue when excluding than it is including!

Also, note the revision to MsgBox that notifies too many items!

Hiding the rows will produce a similar result. Hiding rows based on a criteria is not an uncommon question and what I find more interesting is how to use VBA to control the AutoFilter feature in ways that aren't directly supported by manual autofilters (like filtering to exclude more than two items). This thread shows some nice approached based a simple list, and the core code could be extended to handle 3 or more "Contains" or "Ends with ", etc criteria.

Regarding the revision to the Msgbox, my inclusion of the CountLarge test was meant to handle the scenario of the user running the macro with all blank cells below.
I rarely use Selection.End(xlDown) because I typically want all the entire data range below the Activecell including any blanks.
The CountA() alternative doesn't handle that objective, because it will allow the code to continue and try to process the ~ million cells below the ActiveCell.

I like your Msgbox prompt much better as mine was not intended for an end-user!

 

[Garry2Rs]

My point here was the array base (0 vs 1), not the string issue which I have no arg about!

 

[Garry2Rs]

End(xlDown) will return the row above the first blank cif the selection isn't blank. If the selection is blank it will return the first non-blank cell. I normally use End(xlUp) from the bottom, which will included any blanks between there and the top.

I just looked up CountLarge and see your point about CountA. (I wasn't familiar with this prop) In context, would Max() be better for early versions?

 

[Garry2Rs]

After giving my early version more thought I revised it as follows...

Sub FilterExcludes2()
  Const sExcludes$ = "1,2,3,4,5,6"
  Dim vAll, n&
  
  Application.ScreenUpdating = False
    vAll = ActiveSheet.Range(Selection, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
    For n = LBound(vAll) + 1 To UBound(vAll)
      If InStr(1, sExcludes, vAll(n, 1)) > 0 Then Rows(n).Hidden = Not Rows(n).Hidden
    Next 'n
  Application.ScreenUpdating = True
End Sub

..as I understand there's a size limit associated with WorksheetFunction.Transpose. Also, The test for >=10000 isn't required, IMO, for this usage purpose.

 

[Jerry Sullivan]

My point here was the array base (0 vs 1), not the string issue which I have no arg about!

Oh, then we misunderstood what we were not agreeing about. No worries!

I try code in a way that will work regardless of what the Option Base is set since others reusing the code might have a different Option Base.
So I typically don't have a preference between 0 vs 1 for an array base.

Regarding (xlUp) vs (xlDown)...Yes, I prefer xlUp to capture the data range including blanks as noted in post #12.
I used (xlDown) to be consistent with the OP code. The OP might only want to filter the contiguous non-blank cells, if for example they have more than one data set on the same sheet.

If one does use the xlDown, there is the risk of selecting a very large number of cells. Avoiding iterating through that number of cells was the primary reason for my adding that bit of validation.
You're correct that the limitation of Transpose will reduce that set to ~65K cells. In that scenario, I'd still suggest a warning. If the user actually intended to apply this code to a data set of 100K rows, one would want a warning that set of values filtered had been truncated!

CountLarge was added with xl2007 with the introduction of the larger sheet size because Count will have an overflow error if the user selects more than ~2.15 billion cells.

Garry, are you using xl2003? Are you one of those guys who refuses to go to the later versions because you don't like them as much or some other reason?

 

[Garry2Rs]

Thanks for the detailed response, I appreciate that!

I'm a Excel Applications Developer and so I dev in v11/v12, and test as far back as v9. Though there's not many clients using v9/v10, those that are are stubborn about upgrading. I'm a staunch believer in dev'ing in the earliest version users will have, but I stopped supporting anything prior to v9 some years ago. If I'm still at it in 2015 I'll change that up to v11. (v11/v12 are the most popular in use by my clients) I didn't like the Ribbon idea when 1st introduced, though, and so I deliberately set out to dupe all my Excel apps as stand-alone VB6 apps. Now I can have it either way (or both ways)!

My dev machine (XP SP3) has v9 thru 2010-x32, and I'm thinking about putting x64 version on my Win7 Pro machine. So to answer your question.., no, I'm not one who refuses to go with the later version! Unfortunately, I have to cater to client demands and so is why my interest to be able to dupe some of the later features/functionality wherever possible.

My assertion that my initial offering was 'optimized' is about the code not looping dict more than necessary since your loading of vAll resulted in a 1D array that made using Filter() possible. Otherwise, I'd have gone same way I did for my show/hide toggle routine and did same as you for rebuilding an output array, ..just it would be 2D if dumping into a range. Obviously 1D is required/accepted by AutoFilter and so made sense to go with. Fact is, I don't consider speed the only criteria to qualify code as optimized!

<FWIW>
I had to make exclude filtering functionality for a VB6 app that I use fpSpread.ocx with and so is why I offered a 'pseudo' version of that here. (I tried to make it compliment your code) In my example the excludes are hard-coded but in my VB6 app these are selected on the worksheet in a dedicated cell if variable, or input to an options dialog if defaults. In both cases the excludes are user-defined and stored as a delimited string.

 

[Garry2Rs]

I try code in a way that will work regardless of what the Option Base is set since others reusing the code might have a different Option Base.
So I typically don't have a preference between 0 vs 1 for an array base.

I prefer to use 1 based 2D arrays when working with range data. I like zero based arrays otherwise because this reflects the way most list controls are configured. Also, a zero based array is better for db recordsets in that the field names occupy arr(0) and so the UBound is the number of records, beginning with arr(1).

In some cases I use Enum indices to ref elements for fixed arrays regardless of their base (0/1). Depending on application context, I usually go with zero based when building a 1D array of arrays.