Can I remove ALL autofilter dropdown buttons from the entire worksheet?-CodePudding

Hopefully this is an easy one that someone can help with- I know there should be an easy way to do this, but for the life of me I can't figure it out. I have my autofilter successfully running and everything is working nicely, but I hate the look of the autofilter dropdown arrows in my header cells. Is there a way I can remove the dropdowns from the entire worksheet?

Right now I'm just doing it piecemeal, which works, but seems unnecessarily long. Current code is as follows:

    Range("A2:L1000").AutoFilter Field:=4, VisibleDropDown:=False, Criteria1:=Array("FSA Healthcare 01/01/2022 with Carryover", "Dependent Care 01/01/2022", "FSA Limited 01/01/2022 with Carryover"), Operator:=xlFilterValues
    Range("A2:L1000").AutoFilter Field:=5, VisibleDropDown:=False, Criteria1:="CBNU", Operator:=xlOr, Criteria2:="CBU"
    Range("A2:L1000").AutoFilter Field:=6, VisibleDropDown:=False, Criteria1:="Active"
    Range("A2:L1000").AutoFilter Field:=9, VisibleDropDown:=False, Criteria1:=">0"
    'Filters by plan type, status, division
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "Admin Notes"
    Range("J2").Font.Bold = True
    Range("J2").Interior.Color = vbYellow
    Range("J2").BorderAround , LineStyle:=xlContinuous, Weight:=xlThin
    'Adds "Admin Notes" text
    Range("A2:L1000").AutoFilter Field:=1, VisibleDropDown:=False
    Range("A2:L1000").AutoFilter Field:=2, VisibleDropDown:=False
    Range("A2:L1000").AutoFilter Field:=3, VisibleDropDown:=False
    Range("A2:L1000").AutoFilter Field:=7, VisibleDropDown:=False
    Range("A2:L1000").AutoFilter Field:=8, VisibleDropDown:=False
    Range("A2:L1000").AutoFilter Field:=10, VisibleDropDown:=False
    Range("A2:L1000").AutoFilter Field:=11, VisibleDropDown:=False
    Range("A2:L1000").AutoFilter Field:=12, VisibleDropDown:=False
    'Removes filter dropdown button

It seems like this is not very efficient since I have to basically repeat this block of code for multiple sheets and at least 8 lines are doing the same thing, but I'm not sure how to improve it.

CodePudding user response：

Do you need to use the dropdown arrows at all or can you set up the filters without them? Link attached gives a good example of how to set up filters without arrows.

https://www.extendoffice.com/documents/excel/4834-excel-hide-filter-arrows.html

Here is the VBA code for the advanced filter option.


Sub Macro2()
'
' Macro2 Macro
'

'

    Range("A1:AP8").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("L1:L8"), Unique:=False
    ActiveSheet.Range("$B$2:$M$8").AutoFilter Field:=11, Criteria1:="enter criteria here"

End Sub

CodePudding user response：

The easiest way would be to use tables/listobjects - there you have ShowAutoFilterDropdown, see https://stackoverflow.com/a/54535009/16578424.

If you have a normal range it is a bit more complex.

First of all: yes, you have to set visibledropdown per each column. And yes, you have to do it for all columns individually. Another challenge: You want to keep the criterias, if a filter was chosen.

But you can build a generic sub that takes a range and handles the filter settings.

Public Sub hideRangeFilterArrows(rg As Range)

If Not rg.ListObject Is Nothing Then
    hideListObjectFilterArrows rg.ListObject
Else
    Dim ws As Worksheet: Set ws = rg.Parent
    If ws.AutoFilterMode = False Then Exit Sub
    
    Dim af As AutoFilter, f As Filter
    Set af = ws.AutoFilter

    Dim i As Long, c As Range
    For Each c In rg.Rows(1).Cells
        i = i   1
        Set f = af.Filters(i)
        If f.On = True Then  'we want to keep the criteria
            If f.Operator = 0 Then   'simple filter
                c.AutoFilter field:=i, Criteria1:=f.Criteria1, visibledropdown:=False
            ElseIf f.Operator <> xlFilterDynamic And f.Operator <> xlFilterValues Then
                'those two filters are too complex to handle here
                c.AutoFilter field:=i, Criteria1:=f.Criteria1, Operator:=f.Operator, Criteria2:=f.Criteria2, visibledropdown:=False
            End If
        Else    'easy - there is no filter on this column
            c.AutoFilter field:=i, visibledropdown:=False
        End If
    Next

End If
End Sub

Public Sub hideListObjectFilterArrows(lo As ListObject)
If Not lo.AutoFilter Is Nothing Then
    lo.ShowAutoFilterDropDown = False
End If
End Sub

You can then call this generic sub for your specific range, e.g. like this

hideListObjectFilterArrows ActiveSheet.Range("A2:L1000")