实例需求:数据表从A4开始,列数和行数不确定,用户使用B1:D2(下文简称为用户过滤器,以区别与表格的筛选器,过滤器使用Excel数据有效性功能实现下拉列表)实现对于数据表格的定制筛选,如果需要可以自行增减过滤器个数,过滤器先后顺序不一定和表格标题行相同。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Application.Intersect(Target, Me.Range("B2:D2")) Is Nothing Then
Dim vCol, vCrit
Const HEADER = 4
vCol = Application.Match(Target.Offset(-1), Me.Rows(HEADER), 0)
If VBA.IsError(vCol) Then Exit Sub
With Me.Range("A4").CurrentRegion
If Target.Value = "All" Then
If Me.AutoFilterMode Then
.AutoFilter Field:=vCol
End If
Else
vCrit = Target.Text
If IsDate(vCrit) Then
.AutoFilter Field:=CLng(CDate(vCol)), Criteria1:=vCrit, Operator:=xlAnd
Else
.AutoFilter Field:=vCol, Criteria1:=vCrit, Operator:=xlFilterValues
End If
End If
End With
End If
End Sub
【代码解析】
第2行代码使用CountLarge
属性判断发生改变的单元格个数,如果多个单元格发生变化,则结束执行Change事件代码。
第3行代码判断发生变化的单元格是否位于B2:D2范围之内。
第5行代码声明常量,指定数据表标题行的位置。
第6行代码调用工作表函数Match
在标题行查找相应的筛选器名称。
如果无法定位到相应的标题字段名称,第7行代码停止事件代码执行。
第8行代码使用CurrentRegion
属性获取数据表的Range对象引用。
第9行代码判断变化单元格内容是否为All
。
如果满足条件,第10~12行代码取消相应列的筛选器。
如果不满足条件,第14~19行代码在指定设置筛选器。
对于日期列,使用第16行代码设置筛选器,其他类型数据则使用第18行代码设置筛选器。