VB Autofilter Field Criteria

[jeefreak]

Hi Guys,

Selection.AutoFilter Field:=9, Criteria1:="Clear"

In the above code, Field 9 refers to the 9th column. In my table each column does have a header name. Column 9's header is "KIND". From time to time this KIND column may move around in the table. Is there a way to filter based on the column header name and not the columns placement?

Selection.AutoFilter Field:="KIND", Criteria1:="Clear"

I know the above does not work, but is there something similar that's available? Or maybe a way to dynamically index the columns?

 

[Marcelo Branco]

Hi,

Not elegant but it seems to work

Sub Test()
    Dim myCol As Long
    
    With Sheet1
        myCol = .Range("Table1[#All]").Find(What:="Kind", After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
    End With
    
    Range("Table1[#All]").Select
    Selection.AutoFilter Field:=myCol, Criteria1:="Clear"
End Sub

HTH

M.

 

[Marcelo Branco]

Hi,

My previous version had some flaws. Try this

Sub TestFilter()
    Dim myCol As Long
 
    With Sheet1
        myCol = .Range("Table1[#All]").Find(What:="Kind", _
            After:=.Range("Table1[#All]").Cells(1, 1), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Column
 
        myCol = myCol - Range("Table1[#All]").Column + 1
    End With
 
    Range("Table1[#All]").Select
    Selection.AutoFilter Field:=myCol, Criteria1:="Clear"
End Sub

M.