Hide Arrows in Excel AutoFilter

When you turn on the filter in an Excel worksheet list, or if you create a named Excel table, each cell in the heading row automatically shows a drop down arrow. The arrow button shows a filter icon, if you have filtered that column, and if you point to the arrow button it shows the filter criteria.

autofilterarrows01

You can leave all the arrows showing, or remove the AutoFilter, or use programming to hide one or more of the arrows.

Manually Remove the AutoFilter

For either a worksheet list, or a named table, you can manually turn the AutoFilter on and off, which also shows or hides the arrow buttons.

  1. Click any cell in the filtered range
  2. On the Excel Ribbon, click the Data tab
  3. Click the Filter button.

autofilterribbon01

Hide AutoFilter Arrows with VBA

Before Lists and Named Tables were added to Excel, there could only be one AutoFilter per worksheet. Now, in addition to the single worksheet AutoFilter, you can put multiple named tables on a sheet. Each of those tables has its own AutoFilter property.

There are code examples below, for hiding arrows in a List AutoFilter, and a worksheet AutoFilter. There are more AutoFilter VBA examples on my Contextures website:

Hide the List AutoFilter Arrows With VBA

If you want to leave one or more arrows visible, but hide the others, you can use VBA. In this example, only the second column will have an arrow, and all the others arrows will be hidden.

This code is designed for a named table, which has its own AutoFilter property. There can be multiple named tables on a worksheet, and each one’s AutoFilter settings can be different.

Sub HideArrowsList1()
'hides all arrows except list 1 column 2
Dim Lst As ListObject
Dim c As Range
Dim i As Integer
Application.ScreenUpdating = False

Set Lst = ActiveSheet.ListObjects(1)
i = 1

For Each c In Lst.HeaderRowRange
 If i <> 2 Then
    Lst.Range.AutoFilter Field:=i, _
      VisibleDropDown:=False
 Else
     Lst.Range.AutoFilter Field:=i, _
      VisibleDropDown:=True
 End If
 i = i + 1
Next

Application.ScreenUpdating = True
End Sub

Hide the Worksheet AutoFilter Arrows With VBA

To hide the arrows for a worksheet table’s AutoFilter, the code is slightly different. There can be only one worksheet AutoFilter on a worksheet, and in this example, the filtered list starts in cell A1. The following procedure hides the arrows for all columns except column B.

Sub HideArrows() 
'hides all arrows except column 2
Dim c As Range 
Dim i As Integer 
i = Cells(1, 1).End(xlToRight).Column 
Application.ScreenUpdating = False 

For Each c In Range(Cells(1, 1), Cells(1, i))
 If c.Column <> 2 Then 
  c.AutoFilter Field:=c.Column, _ 
    Visibledropdown:=False 
 End If 
Next 

Application.ScreenUpdating = True 
End Sub

Use the Hide Arrow Macros

To use these macros, copy them into a regular code module in your workbook. There are instructions here.

You would only have to run the code once, after you set up the filtered list. To run the code,

  • Click the View tab on the Excel Ribbon
  • At the far right of the tab, click the Macros command (click the picture at the top of the command)
  • In the list of macros, click the macro that you want to run
  • Click the Run button.

________________

You may also like...

4 Responses

  1. William Donnelly says:

    I pasted this code but it does not seem to have the desired effect.
    Besides the code, is there anything else I need to do to make the autofilter *use* this subroutine?
    Seems that something, somewhere, would need to call HideArrows(), and how would Excel know to do that?

  2. Chris in Tokyo says:

    Worked perfectly. i needed this for reporting purposes, as i have to paste excel tables in Powerpoint, and the arrows are unsightly. Thank you for this. Granted, i had to modify the code to hide all arrows but that was easy to do. Thank you!

  1. May 31, 2012

    […] Hide Arrows in Excel AutoFilter […]

Leave a Reply to Chris in Tokyo Cancel reply

Your email address will not be published. Required fields are marked *