How to filter out VBA with multiple criteria

Question

My problem is that I want to filter out a specific value in different column of sheet, I tried record macro but the record feature seems to only support selecting specific values to keep instead of take out.

For example, if the column is filled with weekly data’s which is not known beforehand, I want to make sure a specific category is not in there. If a sheet was filled weekly data, with columns headings like Company, Product, Id,Date of manufacturing, manager name and all .

From the column for company i want to take out "ABC company" ,”FAST ltd”,”WIN Company” without having to know the name of other companies in list. Also I want to take out some products like “computer”,’phone”,” laptop”,” sim”. The record feature in macro only seems to say what to keep, not what to take away . I would have to select every company except the 3 companies which I want to filter out.

Please help me to get the code for VBA for filter out using multiple criteria.

I've tried this code

ActiveSheet.Range("$A$1:$G$24").AutoFilter Field:=2, Criteria1:="<>ABC ccompany", _
Operator:=xlAnd, Criteria2:="<>WIN company", Operator:=xlAnd, Criteria3:="<>FAST ltd"

But I am getting "run time error 1004".I think Criteria method allows only two criteria at a time.

Any help would be great.

Just to be sure...you have "ABC ccompany" (typo error) in your example, when it should be "ABC company" (I'm assuming). Check out this post from this board. (https://stackoverflow.com/questions/29413433/vba-autofilter-text-filters-with-multiple-criteria-of-begin-with) — Mitch, Aug 13 '17 at 12:25

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Solution hiding the lines themselves:

Option Explicit

Sub hide_values(Company As Variant, Product As Variant)
    On Error GoTo hell
    
    Dim myList As ListObject, myLine As Range, myValues As Variant
    Set myList = Me.ListObjects(1) ' assuming you have just one table on your page
    For Each myLine In myList.DataBodyRange.Rows
        myValues = myLine.Value
        myLine.EntireRow.Hidden = False
        If myValues(1, 1) = Company Then
            myLine.EntireRow.Hidden = True
        End If
        Else If myValues(1, 2) = Product Then
            myLine.EntireRow.Hidden = True
        End If
    Next myLine
    
    End
hell:
    Debug.Assert False
    Resume
End Sub

Sub test_it()
    ActiveSheet.hide_values "FAST ltd", "computer"
End Sub

This sollution is incomplete because it only excludes one Company and one product, but it can be extended by passing a set of strings for each and looping through these sets.

score 0 · Answer 2 · answered Aug 13 '17 at 12:37

0

As an alternative to AutoFilter, I found this from @Gary's Student to hide or show entire rows on the sheet. Very simple and easily modifiable.

Sub ThreeWay()
Dim rng As Range, r As Range
Set rng = Range("A2:A25")

  For Each r In rng
    v = Left(r.Value, 1)
      If v = "A" Or v = "D" Or v = "M" Then
          r.EntireRow.Hidden = False
      Else
          r.EntireRow.Hidden = True
      End If
  Next r
End Sub

answered Aug 13 '17 at 12:37

Mitch

573
3
13

That is what I did in **Sollution hiding the lines themselves**. My version is (after the extension I mentioned but did not elaborate) more flexible, but your version is easier, and therefore more suited for DYVIA, I beleave. – Dirk Horsten Aug 13 '17 at 12:44

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

Solluction setting the filters

Sub hide_values(Company As Variant, Product As Variant)
    On Error GoTo hell
    
    Dim myList As ListObject, myLine As Range, myValues As Variant
    Set myList = Me.ListObjects(1) ' assuming you have just one table on your page
    myList.Range.AutoFilter Field:=1, Criteria1:="<>" & Company
    ' , Operator:=xlAnd, Criteria2:="<"&OtherCompany
    
    myList.Range.AutoFilter Field:=1, Criteria1:="<>" & Product
hell:
    Debug.Assert False
    Resume
End Sub

Sub test_it()
    ActiveSheet.hide_values "FAST ltd", "computer"
End Sub

This sollution is incomplete because it only excludes one Company and one product. Extending it to exluding two porducts and companies is evident (see the commented out code), but extending it further is requires completely different code to set the filters. (Let me know if you need me to elaborate that.)

The advantage of this option is: the user keeps control over the selection after the subroutine ran.

How to filter out VBA with multiple criteria

3 Answers3

Solution hiding the lines themselves:

Solluction setting the filters