VBA如何设置多条件筛选
时间: 2024-05-20 12:07:09 浏览: 13
以下是VBA设置多条件筛选的示例代码:
```VBA
Sub MultiCriteriaFilter()
Dim rngData As Range
Dim rngCriteria As Range
Dim rngOutput As Range
Set rngData = Range("A1:C10") ' 数据范围
Set rngCriteria = Range("E1:G2") ' 条件范围
Set rngOutput = Range("I1") ' 输出范围
' 复制条件到数组
Dim arrCriteria() As Variant
arrCriteria = rngCriteria.Value
' 构造筛选条件
Dim strCriteria As String
For i = 1 To UBound(arrCriteria,1)
If i > 1 Then strCriteria = strCriteria & ","
strCriteria = strCriteria & arrCriteria(i, 1) & ":" & arrCriteria(i, 2)
Next i
' 执行筛选
rngData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rngCriteria, _
Unique:=False
' 统计结果
Dim arrResult() As Variant
arrResult = Application.WorksheetFunction.SumProduct(rngData.SpecialCells(xlCellTypeVisible))
' 输出结果
rngOutput.Value = arrResult
' 清除筛选
rngData.AutoFilter
End Sub
```
该代码使用AdvancedFilter函数进行多条件筛选,将筛选结果统计后输出到指定单元格。其中,rngData为数据范围,rngCriteria为条件范围,rngOutput为输出范围。代码首先将条件复制到数组中,然后构造筛选条件,执行筛选,统计结果,最后输出结果并清除筛选。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![xlsm](https://img-home.csdnimg.cn/images/20210720083646.png)