filter out multiple criteria using excel vba

by

I think (from experimenting – MSDN is unhelpful here) that there is no direct way of doing this. Setting Criteria1 to an Array is equivalent to using the tick boxes in the dropdown – as you say it will only filter a list based on items that match one of those in the array.

Interestingly, if you have the literal values "<>A" and "<>B" in the list and filter on these the macro recorder comes up with

Range.AutoFilter Field:=1, Criteria1:="=<>A", Operator:=xlOr, Criteria2:="=<>B"

which works. But if you then have the literal value "<>C" as well and you filter for all three (using tick boxes) while recording a macro, the macro recorder replicates precisely your code which then fails with an error. I guess I’d call that a bug – there are filters you can do using the UI which you can’t do with VBA.

Anyway, back to your problem. It is possible to filter values not equal to some criteria, but only up to two values which doesn’t work for you:

Range("$A$1:$A$9").AutoFilter Field:=1, Criteria1:="<>A", Criteria2:="<>B", Operator:=xlAnd

There are a couple of workarounds possible depending on the exact problem:

  1. Use a “helper column” with a formula in column B and then filter on that – e.g. =ISNUMBER(A2) or =NOT(A2="A", A2="B", A2="C") then filter on TRUE
  2. If you can’t add a column, use autofilter with Criteria1:=">-65535" (or a suitable number lower than any you expect) which will filter out non-numeric values – assuming this is what you want
  3. Write a VBA sub to hide rows (not exactly the same as an autofilter but it may suffice depending on your needs).

For example:

Public Sub hideABCRows(rangeToFilter As Range)
  Dim oCurrentCell As Range
  On Error GoTo errHandler

  Application.ScreenUpdating = False
  For Each oCurrentCell In rangeToFilter.Cells
    If oCurrentCell.Value = "A" Or oCurrentCell.Value = "B" Or oCurrentCell.Value = "C" Then
      oCurrentCell.EntireRow.Hidden = True
    End If
  Next oCurrentCell

  Application.ScreenUpdating = True
  Exit Sub

errHandler:
    Application.ScreenUpdating = True
End Sub

More Related Contents:

  1. Cannot VBA write data to cells in Excel 2007/2010 within a function
  2. Excel: the Incredible Shrinking and Expanding Controls [closed]
  3. How to add a custom Ribbon tab using VBA?
  4. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  5. How to use workbook.saveas with automatic Overwrite
  6. Closing Excel Application using VBA
  7. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  8. Excel: Searching for multiple terms in a cell
  9. Excel error 1004 “Unable to get …. property of WorksheetFunction class” appearing inconsistently
  10. Find last row in range
  11. VBA equivalent to Excel’s mod function
  12. Excel VBA to match and line up rows
  13. Excel Date Conversion from yyyymmdd to mm/dd/yyyy
  14. Excel VBA Run-time error ’13’ Type mismatch
  15. Excel VBA wildcard search
  16. VBA Excel – IFERROR & VLOOKUP error
  17. How to programmatically code an ‘undo’ function in Excel-Vba? [duplicate]
  18. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  19. Delete all data rows from an Excel table (apart from the first)
  20. How does one restore default case to a variable in VBA (Excel 2010)?
  21. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  22. Excel VBA Run-time Error ‘32809’ – Trying to Understand it
  23. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  24. How do I use FileSystemObject in VBA?
  25. How to copy sheets to another workbook using vba?
  26. VBA Many buttons point to the same _Click sub
  27. How to suppress Update Links warning?
  28. Handle cancellation of InputBox to select range
  29. VBA: How to combine two ranges on different sheets into one, to loop through
  30. Concatenate multiple ranges using vba

Leave a Comment