时间戳

首页 > 解决方案 > 为什么 .SpecialCells().Count 在区域中没有可见行时总是返回 1？

excel - 为什么 .SpecialCells().Count 在区域中没有可见行时总是返回 1？

问题描述

我有一个 Excel 表（VBA - Office 2013），我在其中设置了自动过滤器。然后我的脚本读取许多可见行并将其复制到另一张纸上。

With stacked_sheet
            .AutoFilterMode = False
            .Range("A1:I1000").AutoFilter Field:=6, Criteria1:=uBoards(b, 1)
            .Range("A1:I1000").AutoFilter Field:=9, Criteria1:=uCombos(c, 1)
        End With
        'Counting number of rows post filtering based on Column 9 (boards)
        filtered_row_count = stacked_sheet.Range("A2:I1000").Columns(9).SpecialCells(xlCellTypeVisible).Count

只要过滤器后有记录，此脚本就可以按预期工作。但是，当过滤器后没有记录时，“ filtered_row_count ”总是返回 1 而不是 0。

下图显示了过滤后的记录（无记录）

在此处输入图像描述

任何帮助表示赞赏。我也尝试了以下变化，但没有一个奏效。. .

1. filtered_row_count = stacked_sheet.Range("A2:1000").Rows.SpecialCells(xlCellTypeVisible).Count
2. filtered_row_count = stacked_sheet.Range("A2:1000").Columns.SpecialCells(xlCellTypeVisible).Count
3. filtered_row_count = stacked_sheet.Range("A2:1000").Cells.SpecialCells(xlCellTypeVisible).Count

标签： excelvba

解决方案

复制SpecialCells( WorksheetFunction.SubTotal)

链接（微软）

提示

  • 在应用过滤器之前创建必要的范围引用。
  • 如果您正在按非空白值进行过滤，则可以使用WorksheetFunction.SubTotal来计算过滤单元格的数量，但您希望在没有标题的区域的一列（过滤器列 ( Field)）中执行此操作。Data Range请注意，您始终可以按“On Error Resume Next方式”进行操作（第二个示例）。
  • 确定单元格数大于 0 后，如果需要复制表头，可以继续复制并.SpecialCells(xlCellTypeVisible)应用到, 或应用到整个区域。Data Range
  • 这些示例中的某些范围引用可能是多余的，但保留在代码中以更好地理解应考虑的内容。一旦你决定走哪条路，删除多余的引用。
  • 在第二个示例中，重点是获取单元格的数量。在On Error...代码块中，您通常会使用Set sfdrg = sdrg.SpecialCells(xlCellTypeVisible)，因为您不关心单元格的确切数量。您只需要知道是否有任何数量的单元格，这由If Not sfdcrg Is Nothing Then（通常If Not sfdrg Is Nothing Then）确定。
Option Explicit

Sub FilterRangeSubTotal()
    
    Dim srg As Range ' Source Range
    Dim sdrg As Range ' Source Data Range (Source Range Without Headers)
    Dim sfdrg As Range ' Source Filtered Data Range
    Dim sdcrg As Range ' Source Data Column Range
    Dim sfdcrg As Range ' Source Filtered Data Column Range
    
    With StackedSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        Set srg = .Range("A1:I1000")
        Set sdrg = srg.Resize(srg.Rows.Count - 1).Offset(1) ' "A2:I1000"
        ' To be able to use 'WorksheetFunction.SubTotal', pick the column
        ' where values in all filtered cells are ensured,
        ' i.e. where you do not filter by blanks e.g.:
        Set sdcrg = sdrg.Columns(9) ' "I2:I1000"
    End With
        
    srg.AutoFilter Field:=6, Criteria1:=uBoards(b, 1) ' ???
    srg.AutoFilter Field:=9, Criteria1:=uCombos(c, 1) ' ???
    
    Dim FilteredCellsCount As Long
    FilteredCellsCount = WorksheetFunction.Subtotal(103, sdcrg) ' COUNTA
    
    If FilteredCellsCount > 0 Then
        
        'Set sfdrg = sdrg.SpecialCells(xlCellTypeVisible)
        'sfdrg.Copy AnotherSheet.Range("A2")
        
        Set sfdcrg = Nothing
    'Else 'FilteredCellsCount = 0
    End If
    
    StackedSheet.AutoFilterMode = False
    
    MsgBox "Filtered Cells Count: " & FilteredCellsCount

End Sub


Sub FilterRangeOnErrorResumeNext()
    
    Dim srg As Range ' Source Range
    Dim sdrg As Range ' Source Data Range (Source Range Without Headers)
    Dim sfdrg As Range ' Source Filtered Data Range
    Dim sdcrg As Range ' Source Data Column Range
    Dim sfdcrg As Range ' Source Filtered Data Column Range
    
    With StackedSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        Set srg = .Range("A1:I1000")
        Set sdrg = srg.Resize(srg.Rows.Count - 1).Offset(1) ' "A2:I1000"
        Set sdcrg = sdrg.Columns(9) ' "I2:I1000", but you can pick any (valid)
    End With
        
    srg.AutoFilter Field:=6, Criteria1:=uBoards(b, 1) ' ???
    srg.AutoFilter Field:=9, Criteria1:=uCombos(c, 1) ' ???
    
    ' 'Rows.Count' will not work because the range is possibly non-contiguous
    ' and only the rows of the first area will be considered.
    ' So you have to use 'Cells.Count' in one column only (sdcrg).
    ' It will never be 0, because if no cell, an error will occur.
    
    On Error Resume Next
    Dim sfdcrg As Range: Set sfdcrg = sdcrg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    Dim FilteredCellsCount As Long

    If Not sfdcrg Is Nothing Then
        FilteredCellsCount = sfdcrg.Cells.Count ' not 'sfdcrg.rows.count'
        
        'Set sfdrg = sdrg.SpecialCells(xlCellTypeVisible)
        'sfdrg.Copy AnotherSheet.Range("A2")
        
        Set sfdcrg = Nothing
    'Else
        'FilteredCellsCount = 0
    End If
    
    StackedSheet.AutoFilterMode = False
    
    MsgBox "Filtered Cells Count: " & FilteredCellsCount

End Sub

推荐阅读