excelvbaautofilterexcel-tables

How to filter an Excel Table with Column Name & Criteria?


I'm trying to run a macro that will pull data from a Table in Excel, when the Table might initially be filtered.

The code should:

  1. Clear the Table filter
  2. Apply a filter to the Column Heading: "STATUS" Criteria1:= "O"
  3. Then run the rest of the code.

I want to use the Column Header name: "STATUS", because the Table has changed a few times, and the Index could also change.

Through various researches, and attempts, I think I have the correct line that will clear the filter WITHOUT removing filters from the table.
However, I am now getting the: "AutoFilter method of Range class failed" on Step 2:

Sub OpenPOs()
    
Dim wbInventory As Workbook
Set wbInventory = Workbooks("IWI Inventory MASTER Report.xlsb")
    
   With wbInventory
      With wsPOTracker 'This is a Code name for worksheet within the workbook
                
        'Clear the Table Filter
         If Not .ListObjects("Table1").AutoFilter Is Nothing Then .ListObjects("Table1").AutoFilter.ShowAllData
               
         'Apply Table Filter to the Column: "STATUS" Criteria = "O"
         .ListObjects("Table1").ListColumns("STATUS").Range.AutoFilter , Criteria1:="O" '<Run-time error '1004': AutoFilter method of Range class failed

 'Rest of the code

Links I've tried to research/copy from:

Clear filter from Table

Auto filter method of range class failed with Criteria

Auto filter method of range class failed


Solution

  • Try this:

    Dim lo As ListObject
        
    Set lo = wsPOTracker.ListObjects("Table1") 'reference the listobject/table
        
    If Not lo.AutoFilter Is Nothing Then lo.AutoFilter.ShowAllData
        
    lo.Range.AutoFilter Field:=lo.ListColumns("STATUS").Index, Criteria1:="O"