Sum Filtered Column Within a Table

Question

Within Excel Vba, I'm conducting a filter on table column referencing a ListObject;

With TableX
.Range.AutoFilter Field:=15, Criteria1:="100"
End With

I want to test the filtered column before executing further code, as it is possible the filter may return no rows matching the value of 100. I've found some information about TotalsCalculation method but struggling to code it. Any thoughts on what I'm missing in this simple example??

score 0 · Answer 1 · answered Jan 17 '18 at 23:16

0

The below code will test if there are zero rows. In order to be able to use the SpecialCells(xlCellTypeVisible) method without fail, I include the headers rows which will always be there, thus if the visible cell row count is > 1 you can be assured there is at least one value of 100.

With TableX

    .Range.AutoFilter Field:=15, Criteria1:="100"

    If Intersect(.Range, .ListColumns(15).Range).SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
        'do stuff
    End If

End With

answered Jan 17 '18 at 23:16

Scott Holtzman

27,099
5
37
72

If the first data row isn't visible, the expression will return `1` since only the first area of the range is considered when using `.Rows.Count` (or `.Columns.Count`). You probably meant `.Cells.Count`. – VBasic2008 Jan 05 '22 at 10:59

VBasic2008 · Answer 2 · 2022-01-05T11:32:59.387

Reference Visible Excel Table Rows

Dim vrg As Range ' Visible Range

With Sheet1.ListObjects("Table1") ' tbl
    
    If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
    
    .Range.AutoFilter 15, "100"
    
    On Error Resume Next
        Set vrg = .DataBodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    .AutoFilter.ShowAllData
    
End With

If Not vrg Is Nothing Then ' visible rows found
    ' Continue, e.g.:
    Debug.Print vrg.Address(0, 0)
    'vrg.Delete Shift:=xlShiftUp ' delete visible rows

Else
    Debug.Print "No visible rows found."
End If

Sum Filtered Column Within a Table

2 Answers2

Reference Visible Excel Table Rows