用VBA可以做到,效果如下
代码如下
不懂就追问
就是不知道在VBA中怎么替换筛选内容为A1单元格
追答在工作表中首先定义名称:mydata为OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$65535),COUNTA(Sheet1!$2:$2),然后在工作表的事件函数中添加如下代码(具有通用性):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Dim tiaojian, quyu As String
tiaojian = [A1]
If tiaojian = "" Then
'mydata为预先定义的名称“mydata=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$65535),COUNTA(Sheet1!$2:$2))”
ActiveSheet.[mydata].AutoFilter Field:=2 '
Else
ActiveSheet.[mydata].AutoFilter Field:=2, Criteria1:=tiaojian
End If
End If
End Sub