CODEWITHSUNDEEP
excelvba
Jonathan
Jonathan

Reputation: 5

How to auto filter a column?

I am trying to filter column "I" which has the header "Stock" to show only columns with a value more than "5" in sheet name "Order in Units".

I get a debugger.

Sub AutoFilter_Number_Examples()
'Examples for filtering columns with NUMBERS
 
    Dim lo As ListObject
    Dim iCol As Long
    'Set reference to the first Table on the sheet
    Set lo = Order in Units.ListObjects(1)
  
    'Set filter field
    iCol = lo.ListColumns("Stock").Index
   
    'Clear Filters
    lo.AutoFilter.ShowAllData

    With lo.Range
        .AutoFilter Field:=iCol, Criteria1:=">5"
    End With
  
End Sub

Upvotes: 0

Views: 191

Answers (1)

Macronaute
Macronaute

Reputation: 196

Is that what you want ? (Column D for example)

Sub AutoFilter_Number_Examples()    
    On Error Resume Next
    Columns("D:D").AutoFilter.ShowAllData
    On error goto 0
    
    Columns("D:D").AutoFilter
    ActiveSheet.Range("D:D").AutoFilter Field:=1, Criteria1:=">5"
End Sub

I add an error handler for showallData because otherwise if data are already unfiltered it will fail.

Upvotes: 1

Related Questions