VBA - Excel Filters - Hide Excel AutoFilter Arrows

Post date: Jul 19, 2011 3:41:52 PM

Perhaps you want users to filter only one of the columns in a table. The following Excel AutoFilter VBA procedure hides the arrows for all columns except column 2.

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

In other cases, you might want to hide the arrows on specific columns, and leave all the other arrows visible. The following procedure hides the arrows for columns 1, 3 and 4.

Sub HideSpecifiedArrows() 'hides arrows in specified columns 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)) Select Case c.Column Case 1, 3, 4 c.AutoFilter Field:=c.Column, _ Visibledropdown:=False Case Else c.AutoFilter Field:=c.Column, _ Visibledropdown:=True End Select Next Application.ScreenUpdating = True End Sub

To show all the arrows, you can use the following Excel AutoFilter VBA code:

Sub ShowArrows() 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)) c.AutoFilter Field:=c.Column, _ Visibledropdown:=True Next Application.ScreenUpdating = True End Sub

If your table doesn't start in cell A1, you can specify the heading range, and hide arrows in specific cells in that range.

Sub HideArrowsRange() 'hides arrows in specified range Dim c As Range Dim i As Integer Dim rng As Range Set rng = Range("D14:J14") i = rng.Cells(1, 1).Column - 1 Application.ScreenUpdating = False For Each c In Range("D14:J14") Select Case c.Address Case "$E$14", "$G$14", "$J$14" c.AutoFilter Field:=c.Column - i, _ Visibledropdown:=False Case Else c.AutoFilter Field:=c.Column - i, _ Visibledropdown:=True End Select Next Application.ScreenUpdating = True End Sub

If your table doesn't start in cell A1, you can specify the heading range, and show all the arrows in that range.

Sub ShowArrowsRange() 'shows arrows in specified range Dim c As Range Dim i As Integer Dim rng As Range Set rng = Range("D14:J14") i = rng.Cells(1, 1).Column - 1 Application.ScreenUpdating = False For Each c In Range("D14:J14") c.AutoFilter Field:=c.Column - i, _ Visibledropdown:=True Next Application.ScreenUpdating = True End Sub