Excel VBA解读（52）：自动筛选——AutoFilter方法

 

在面对大量数据时，我们可以使用Excel的筛选功能，滤出我们需要的信息。在本文中，我们先从Excel中的“筛选”命令谈起。

 

如下图所示的工作表，将活动单元格置于任一数据单元格中，单击功能区中的“排序和筛选”中的“筛选”命令，可以看到表头单元格中出现了筛选下拉箭头。

 

上述操作录制的VBA代码如下：

Sub Macro1()

'

' Macro1 Macro

'

 

'

    Selection.AutoFilter

End Sub

 

接着操作。单击内容为“语文”的下拉箭头（即表头第3列），选择“数字筛选——大于(G)…”，在“自定义自动筛选方式”对话框的“显示行”中，第一个组合框左侧选择“大于或等于”，右侧输入“80”，第二个组合框左侧选择“小于”，右侧输入“90”，即筛选语文分数大于或等于80且小于90的数据，结果如下图所示。

录制的代码如下：

Sub Macro2()

'

' Macro2 Macro

'

 

'

    Selection.AutoFilter

   ActiveSheet.Range('$A$1:$F$19').AutoFilter Field:=3,Criteria1:='>=80', _

        Operator:=xlAnd,Criteria2:='<90'

End Sub

 

观察上面录制的代码，可以看出，Excel VBA使用AutoFilter方法来实现“筛选”功能，并提供了一系列可选的参数来进一步执行筛选操作。

 

AutoFilter方法的语法及说明

下面是Range对象的AutoFilter方法的语法：

Range对象.AutoFilter(Field,Criterial1,Operator,Criteria2,VisibleDropDown)

说明：

1. 参数Field，指定想要基于筛选的字段的整数偏移量。从列表的左侧算起，最左侧的字段是字段一。

2. 参数Criterial1，指定判断条件（为字符串）。使用“=”查找空字段，或者使用“<>”查找非空字段。如果忽略该参数，那么判断是全部。如果参数Operator是xlTop10Items，那么参数Criterial1指定项目的数量。

3. 参数Operator，指定筛选的类型，为XlAutoFilterOperator常量之一：

• xlAnd：值为1。Criteria1和Criteria2的逻辑与。

• xlOr：值2。Criteria1或Criteria2的逻辑或。

• xlTop10Items：值3。显示最大值的项（在Criteria1中指定项目数）。

• xlBottom10Items：值4。显示最小值的项（在Criteria1中指定项目数）。

• xlTop10Percent：值5。显示最大值的项（在Criteria1中指定百分比）。

• xlBottom10Percent：值6。显示最小值的项（在Criteria1中指定百分比）。

• xlFilterValues：值7。筛选值。

• xlFilterCellColor：值8。单元格的颜色。

• xlFilterFontColor：值9。字体颜色。

• xlFilterIcon：值10。筛选图标。

• xlFilterDynamic：值11。动态筛选。

• 参数Criteria2，指定第二个判断条件（字符串），使用Criterial1和Operator构建复合判断条件。

• 参数VisibleDropDown，设置为True则显示所筛选字段的自动筛选下拉箭头；设置为False则隐藏所筛选字段的自动筛选下拉箭头。默认为True。

• 如果忽略所有参数，那么AutoFilter方法简单地切换指定区域的自动筛选下拉箭头的显示。

 

示例1：移除自动筛选提供的下拉箭头

在Excel中使用自动筛选时，会在每列顶部都添加一个下拉箭头以获取相应的筛选项。有时，我们只需要使用其中某些字段的下拉箭头，不需要其它字段带有下拉箭头，以免误操作这些字段。例如，上面的示例中，我们只需要代表科目的语文、数学、英语、历史字段有下拉箭头，而移除列A、列B中的下拉箭头。代码如下：

Sub testAutoFilter1()

    Range('A1').AutoFilter Field:=1,VisibleDropDown:=False

    Range('A1').AutoFilter Field:=2,VisibleDropDown:=False

End Sub

即，将相应列字段的参数VisibleDropDown设置为False。

执行后的效果如下图：

 

示例2：一次执行多个列字段的筛选

仍以本文开头的工作表为例，要求得到语文成绩大于等于80的男生的数据记录。代码如下：

Sub testAutoFilter2()

    Range('A1').AutoFilter Field:=2,Criteria1:='=男'

    Range('A1').AutoFilter Field:=3,Criteria1:='>=80'

End Sub

 

示例3：复制筛选结果

将示例2中得到的结果复制到以单元格H21开头的区域中。代码如下：

Sub testAutoFilter3()

    Dim lngLastRow As Long

   

    '找到工作表中最后一行

    lngLastRow = Range('A' &Rows.Count).End(xlUp).Row

   

    '按条件执行自动筛选

    Range('A1').AutoFilter Field:=2,Criteria1:='=男'

    Range('A1').AutoFilter Field:=3,Criteria1:='>=80'

    

    '将筛选后的结果复制到指定位置

    Range('A1:F' &lngLastRow).Copy Range('H21')

End Sub

可以看出，Copy方法仅复制可见单元格中的内容。

 

示例4：删除筛选出的数据

如下图所示的工作表，我们要删除列A中单元格内容为“0”的数据行。

此时，我们可以使用AutoFilter方法筛选出这些行，然后进行删除。代码如下：

Sub testAutoFilter4()

    Dim rng As Range

   

    '设置筛选区域

    Set rng = Range('A1:B10')

   

    '如果开启了筛选模式则关闭该模式

    If ActiveSheet.AutoFilterMode = True Then

        ActiveSheet.AutoFilterMode = False

    End If

   

    '筛选列A中内容为0的单元

    rng.AutoFilter Field:=1,Criteria1:='0'

   

    '删除筛选出来的行

    With rng

        .Offset(1).Resize(.Rows.Count -1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlShiftUp

        '关闭筛选模式

        .Worksheet.AutoFilterMode = False

    End With

End Sub

您可能注意到代码中出现了一些我们前面的文章中没有提到的新属性和方法，下面来作些解释。

• AutoFilterMode属性属于Worksheet对象（后续文章中我们将会详细讲解Worksheet对象的属性和方法），用来获取工作表中用户是否使用了自动筛选，或者用来设置工作表中使用自动筛选。如果其值为True，那么表明工作表中当前显示有自动筛选下拉箭头，即使用了自动筛选功能。如果设置其值为False，则取消工作表中的自动筛选，即移除自动筛选的下拉箭头。

• Delete方法用来删除单元格区域，使用参数Shift来移动单元格已取替被删除的单元格。将该参数值设置为xlShiftUp指明将单元格往上移来替换被删除的单元格。

• 从代码运行中我们发现，进行自动筛选后，使用Rows.Count统计时仍然会统计隐藏的行。

代码运行后的结果如下图。

也可以参照下面的视频来加深理解。

 

示例5：根据当前单元格内容筛选数据

如下图所示的工作表，我们要筛选出和当前单元格内容相同的单元格所在的数据行。

例如，当前单元格为单元格B7，当运行程序后，会筛选出与单元格B7中的内容（即“一班”）相同的单元格所在的数据行，所需效果如下图：

代码如下：

Sub testAutoFilter5()

    Dim lngColNum As Long

   

    '计算当前单元格在区域中的列号

    lngColNum = ActiveCell.Column -(ActiveCell.CurrentRegion.Column - 1)

   

    '筛选

    Selection.AutoFilter Field:=lngColNum,Criteria1:=ActiveCell

   

End Sub

注意到本代码中使用了一个技巧，即代码：

    lngColNum = ActiveCell.Column -(ActiveCell.CurrentRegion.Column - 1)

当单元格区域不是以列A为第1列时，可以准确地计算出当前单元格在所处区域中的列号，从而将其运用到接下来的AutoFilter方法的参数Field中。

 

示例6：根据当前单元格内容实时筛选数据并将数据粘贴到指定位置

本示例将示例3和示例5结合，实时筛选与当前单元格内容相同的数据并将数据复制到指定位置。

仍以示例5的工作表为例。当活动单元格处于A2:C9中时，能够实时对数据进行筛选，并将筛选出来的数据复制到以单元格A13开头的单元格区域中。

要实时筛选数据，必须结合工作表事件代码。即，我们的代码放置在了工作表模块的Worksheet_SelectionChange事件（将在Worksheet对象中介绍其详细用法）中：

Private SubWorksheet_SelectionChange(ByVal Target As Range)

    Dim lngColNum As Long

    Dim lngLastRow As Long

    Dim rng As Range

 

    '如果开启了筛选模式则关闭该模式

    If ActiveSheet.AutoFilterMode = True Then

        ActiveSheet.AutoFilterMode = False

    End If

   

    '设置当前单元格与单元格区域A2:C9相重合的单元格

    Set rng = Intersect(Target,Range('A2:C9'))

    '找到工作表中数据所在的最后行

    lngLastRow = Range('A' &Rows.Count).End(xlUp).Row

   

    '如果工作表中第9行外还有数据则清除

    If lngLastRow > 9 Then

        Range('A13:C' &lngLastRow).Value = ''

    End If

   

    If Not rng Is Nothing Then

        '计算当前单元格在区域中的列号

        lngColNum = ActiveCell.Column -(ActiveCell.CurrentRegion.Column - 1)

   

        '筛选

        Selection.AutoFilter Field:=lngColNum,Criteria1:=ActiveCell

        '关闭事件响应

        Application.EnableEvents = False

        Range('A2:C9').CopyRange('A13')

    End If

 

    '关闭筛选模式

    ActiveSheet.AutoFilterMode = False

    '开启事件响应

    Application.EnableEvents = True

End Sub

 

注意，上述代码必须放置在数据所在工作表模块中。此时，当活动单元格处于该工作表A2:C3区域中时，会自动筛选与活动单元格内容相同的单元格数据，并复制粘贴到以单元格A3开始的区域中。

下面是一段简短的演示视频：

 

在代码中，我们使用了语句：

    Application.EnableEvents = False

来关闭事件响应。因为我们的代码是靠事件实时响应来达到动态选择复制的效果，如果在复制前不关闭事件响应，那么复制操作将会引发SelectionChange事件，会达不到我们想要的结果，因此，先关闭事件响应，复制完后再开启，以实现我们再次选择单元格时数据的变化。我们会在Application对象中详细讲解关于EnableEvents属性的内容。

 

--------------------------------------

 

如果您对本文介绍的内容还有什么好的示例，欢迎发送邮件给我：[e-mail]

也可以在本文下方留言，提出您的看法或建议。

本文属原创文章，转载请联系我或者注明出处。