excel自动筛选_在Excel自动筛选器中隐藏箭头

excel自动筛选

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. If you don't need them, here's how you can hide arrows in Excel AutoFilter.

在Excel工作表列表中打开过滤器时，或者在创建命名的Excel表时 ，标题行中的每个单元格都会自动显示一个下拉箭头。 如果不需要它们，可以按照以下方法在Excel自动筛选器中隐藏箭头。

Excel自动筛选箭头 (Excel AutoFilter Arrows)

In the heading row, an AutoFilter arrow button shows a filter icon, if you have filtered that column. If you point to an arrow with a filter icon, a pop up Tool Tip shows the filter criteria.

在标题行中，如果您已经过滤了该列，则“自动过滤器”箭头按钮将显示一个过滤器图标。 如果您指向带有过滤器图标的箭头，则弹出的工具提示将显示过滤器条件。

In the screen shot below, the Customer column has been filtered to show 3 specific customers - FoodMart, MegaStore and VegiVille.

在下面的屏幕快照中，已过滤“客户”列以显示3个特定客户-FoodMart，MegaStore和VegiVille。

自动筛选箭头选项 (AutoFilter Arrow Options)

For an AutoFilter, you have three options for the arrow display:

对于自动筛选器，箭头显示有三个选项：

• Leave all the arrows showing
  保留所有箭头所示
• Remove the AutoFilter, which removes all the arrows
  删除自动筛选，这将删除所有箭头
• 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
   在Excel功能区上，单击“数据”选项卡
3. Click the Filter button.
   单击过滤器按钮。

使用宏隐藏自动筛选箭头 (Hide AutoFilter Arrows with Macro)

Before Lists and Named Tables were added to Excel, there could only be one AutoFilter per worksheet.

在将列表和命名表添加到Excel之前，每个工作表只能有一个自动筛选。

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.

现在，除了单个工作表“自动筛选”之外，您还可以在工作表上放置多个命名表。 这些表中的每个表都有其自己的AutoFilter属性。

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:

下面有一些代码示例，用于在列表自动过滤器和工作表自动过滤器中隐藏箭头。 我的Contextures网站上还有更多AutoFilter VBA示例：

• Excel Worksheet AutoFilter VBA

  Excel工作表自动筛选VBA

• Excel List AutoFilter VBA

  Excel列表自动筛选VBA

使用VBA隐藏列表自动筛选箭头 (Hide List AutoFilter Arrows With VBA)

If you want to leave one or more arrows visible, but hide the others, you can use a macro.

如果要使一个或多个箭头可见，而隐藏其他箭头，则可以使用宏。

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 table's AutoFilter settings can be different.

此代码是为命名表设计的，该表具有自己的AutoFilter属性。 工作表上可以有多个命名表，并且每个表的“自动筛选”设置可以不同。

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

使用VBA隐藏工作表自动筛选箭头 (Hide 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.

一个工作表上只能有一个工作表“自动筛选”，并且在此示例中，筛选后的列表开始于单元格A1中。

The following procedure hides the arrows for all columns except column B.

以下过程隐藏了除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
  单击Excel功能区上的“查看”选项卡
• 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.
  单击运行按钮。

翻译自: https://contexturesblog.com/archives/2012/05/29/hide-arrows-in-excel-autofilter/

excel自动筛选