Excel VBA多条件过滤后可见行SumProduct取值异常求助

我正在开发销售管理用的Excel VBA文件，包含产品表和发票表。报表用户窗体中，用WorksheetFunction.SumProduct计算全部销售总额正常；按时间段过滤发票表后，取可见行范围计算总额也正常。但添加产品编码过滤条件后，新的可见行范围只获取到一条记录，实际过滤后的工作表明明有更多可见记录。

产品表 #

发票表 #

问题代码 #

Private Sub ComboBox1_Change()
    If SD = Empty Then SD = "14010101"
    If FD = Empty Then FD = "14011229"
    
    EOR = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    Sheet1.Range("A1:G" & EOR).AutoFilter 7, "<=" & FD, xlAnd, ">=" & SD
    Set Rng = Sheet1.Range("A2:G" & EOR).SpecialCells(xlCellTypeVisible)
    
    
    TextBox1.Text = Format(WorksheetFunction.SumProduct(Rng.Columns(4), Rng.Columns(5)), "#,##0")
    TextBox2.Text = Format(WorksheetFunction.Sum(Rng.Columns(4)), "#,##0")
    
    If ComboBox1.ListIndex = -1 Then
        TextBox3.Text = ""
        TextBox4.Text = ""
        Sheet1.AutoFilterMode = False
        Exit Sub
    End If
    
    Rng.AutoFilter 3, Val(ComboBox1.Value)
    Set NewRng = Sheet1.Range("A2:G" & EOR).SpecialCells(xlCellTypeVisible)

    TextBox3.Text = Format(WorksheetFunction.SumProduct(NewRng.Columns(4), NewRng.Columns(5)), "#,##0")
    TextBox4.Text = Format(WorksheetFunction.Sum(NewRng.Columns(4)), "#,##0")
    
    Sheet1.AutoFilterMode = False
End Sub

问题原因及修复方案 #

问题根源 #

代码中使用Rng.AutoFilter对已筛选的范围再次添加过滤条件，这会导致仅对Rng中的第一个可见区域应用新过滤，而不是对整个表格的现有过滤条件叠加新规则。因为SpecialCells(xlCellTypeVisible)返回的是多个不连续区域的集合，对它调用AutoFilter只会作用于第一个区域，从而丢失其他可见行。

修复代码 #

直接在原表格的AutoFilter上叠加产品编码过滤条件，而不是对Rng操作：

Private Sub ComboBox1_Change()
    If SD = Empty Then SD = "14010101"
    If FD = Empty Then FD = "14011229"
    
    EOR = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    ' 先清除现有过滤
    Sheet1.AutoFilterMode = False
    ' 应用日期过滤
    Sheet1.Range("A1:G" & EOR).AutoFilter Field:=7, Criteria1:="<=" & FD, Operator:=xlAnd, Criteria2:=">=" & SD
    
    ' 计算日期过滤后的汇总
    Dim dateFilterRng As Range
    On Error Resume Next ' 处理无可见行的情况
    Set dateFilterRng = Sheet1.Range("A2:G" & EOR).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not dateFilterRng Is Nothing Then
        TextBox1.Text = Format(WorksheetFunction.SumProduct(dateFilterRng.Columns(4), dateFilterRng.Columns(5)), "#,##0")
        TextBox2.Text = Format(WorksheetFunction.Sum(dateFilterRng.Columns(4)), "#,##0")
    Else
        TextBox1.Text = "0"
        TextBox2.Text = "0"
    End If
    
    If ComboBox1.ListIndex = -1 Then
        TextBox3.Text = ""
        TextBox4.Text = ""
        Sheet1.AutoFilterMode = False
        Exit Sub
    End If
    
    ' 叠加产品编码过滤条件（直接对表格的AutoFilter操作）
    Sheet1.Range("A1:G" & EOR).AutoFilter Field:=3, Criteria1:=Val(ComboBox1.Value)
    
    Dim productFilterRng As Range
    On Error Resume Next
    Set productFilterRng = Sheet1.Range("A2:G" & EOR).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not productFilterRng Is Nothing Then
        TextBox3.Text = Format(WorksheetFunction.SumProduct(productFilterRng.Columns(4), productFilterRng.Columns(5)), "#,##0")
        TextBox4.Text = Format(WorksheetFunction.Sum(productFilterRng.Columns(4)), "#,##0")
    Else
        TextBox3.Text = "0"
        TextBox4.Text = "0"
    End If
    
    Sheet1.AutoFilterMode = False
End Sub

额外优化点 #

1. 添加On Error Resume Next处理没有可见行的情况，避免运行时错误。
2. 每次操作前先清除现有过滤，确保过滤条件叠加正确。
3. 使用Field:=参数明确指定过滤的列，提高代码可读性。

内容的提问来源于stack exchange，提问作者Samira Sarhadi