I have an excel sheet (VBA - Office 2013) where I set up auto filters. Then my script reads a number of visible rows and copies that to another sheet.

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

This script works as expected as far as there are records after the filter. However, when there are no records after the filter, 'filtered_row_count' always returns 1 instead of 0.

The following image shows the records post filter (no records)

Any help is appreciated. I tried following variations too but none worked . . .

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

CodePudding user response：

Copy SpecialCells (WorksheetFunction.SubTotal)

Links (Microsoft)

• VBA: Range.SpecialCells method (Excel)
• VBA: WorksheetFunction.Subtotal method (Excel)
• Excel: SUBTOTAL function

Tips

• Create the necessary range references before applying the filter.
• If you're filtering by a non-blank value, you can use WorksheetFunction.SubTotal to count the number of filtered cells, but you want to do it in one column (the filter column (Field)) of the Data Range, the range without headers. Note that you can always do it the 'On Error Resume Next way' (second example).
• After you have determined that the number of cells is greater than 0, you can continue to copy with .SpecialCells(xlCellTypeVisible) applied to the Data Range, or applied to the whole range if you need the headers copied.
• Some of the range references in these examples may be redundant but are left in the code to better understand what should be considered. Once you decide which way to go, remove the redundant references.
• In the second example, the focus is on getting the number of cells. In the On Error... block of code, you would usually go with Set sfdrg = sdrg.SpecialCells(xlCellTypeVisible), because you don't care about the exact number of cells. You only need to know if there is any number of cells, which is determined by If Not sfdcrg Is Nothing Then (usually 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

CodePudding user response：

The fix is to change (A2:I1000) to (A1:I1000)

filtered_row_count = stacked_sheet.Range("A1:I1000").Columns(9).SpecialCells(xlCellTypeVisible).Count

The chosen solution below describes it in detail.