Hide Some Filter Drop Down Arrows on AutoFilter

[MikeMcCollister]

I have some tables setup so that when data in one or two columns is changed (in other tables) then the auto filter will be re-run. What I want is that the first column of said tables to not have the drop down arrows. I can make all the drop down arrows to be hidden by using "ShowAutoFilterDropDown = True" but I don't want that. I only want the first column to not have a drop down arrow.

I've tried setting VsibleDropDown:=False but I get "invalid use of property".

Here is my VBA subroutine

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim table As ListObject
    Dim column As Long
   
    ' ??? todo: check if range is in a filtered table and don't do anything

    column = Target.column
   
    ' only do this if changes are made to column "E" or "F"
    If ((column = 5) Or (column = 6)) Then
        For Each table In ActiveSheet.ListObjects
            ' don't waste time on tables that don't have filters
            If (table.ShowAutoFilter) Then
           
                'table.AutoFilter Field:=1, VisibleDropDown:=False 'this gives "invalid use of property"
                'table.ShowAutoFilterDropDown = True 'don't want this
                table.AutoFilter.ApplyFilter
            End If
        Next table
    End If
End Sub

Any ideas how to do this?

Thanks,

Mike

 

[Fluff]

How about

         table.Range.AutoFilter 1, , , , False

 

[MikeMcCollister]

Fluff,

Thank you! I was missing "Range".

This is what works:

table.Range.AutoFilter Field:=1, VisibleDropDown:=False

Regards,

Mike

 

[Fluff]

You're welcome & thanks for the feedback.