vba count visible rows after filter

Question

I keep having issues with visible rows after filtering. I have a table that looks like this:

The name of the table is tstTBL. I am applying 2 filters: filtering for case ID 3 and Filed = Yes, which gives no results. However, when I count the visible rows, it counts 1, although I use only the databodyrange of the table. I am expecting numRows = 0. How should I change the code?

Sub tstFilter()
    Dim filterredRange As Range
    Dim tbl As ListObject
    Dim caseCol As Integer
    Dim trCol As Integer
    Dim numRows As Integer

    Set tbl = Sheet1.ListObjects("tstTbl")
    caseCol = Sheet1.Rows("1:1").Find(What:="Case ID", LookAt:=xlWhole).Column
    tbl.Range.AutoFilter Field:=caseCol, Criteria1:=3

    'check first filter
    On Error Resume Next
        Set filterredRange = tbl.ListColumns("Case ID").DataBodyRange.SpecialCells(xlVisible)
    On Error GoTo 0
    If filterredRange Is Nothing Then
        Debug.Print "no such case"
    End If

    trCol = ActiveSheet.Rows("1:1").Find(What:="Filed", LookAt:=xlWhole).Column
    tbl.Range.AutoFilter Field:=trCol, Criteria1:="Yes"

    'check second filter
    On Error Resume Next
            Set filterredRange = tbl.ListColumns("Filed").DataBodyRange.SpecialCells(xlVisible)
    On Error GoTo 0
    If filterredRange Is Nothing Then
        numRows = 0
    Else
        numRows = filterredRange.Rows.Count
    End If

    Debug.Print numRows

End Sub

Test a `filterredRange.Select` or `Debug.Print filterredRange.Address` to see which is the row that is counted. This will lead you to the issue. • Also note that is must be `xlCellTypeVisible` not `xlVisible` according to the documentation [XlCellType enumeration](https://learn.microsoft.com/en-us/office/vba/api/excel.xlcelltype). — Pᴇʜ, Feb 19 '20 at 09:37
@Pᴇʜ thanks! a printed the address and it gives the address of the first filter. so setting range object (filterredRange ) twice doesn't work. Any idea why? I created a second range variable for the second filter test and it works as expected — Hana, Feb 19 '20 at 09:44
@SJR because I do additional things after the first filter. I didn't include it here because it is not relevant to the question — Hana, Feb 19 '20 at 10:08
Just one more note: Excel has more rows than `Integer` can handle `Dim numRows As Integer` must be of type `Long`. I recommend always to use `Long` instead of `Integer` as [there is no benefit in using integer at all](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520). If you are not sure which one to use always check what you get in return. For example `Rows.Count` returns a `Long` so your variable should be a `Long` too. — Pᴇʜ, Feb 19 '20 at 10:10
@Pᴇʜ, I guess you are right. Though I did think about it and in my file there is no chance I will get to the size of integer.. — Hana, Feb 19 '20 at 10:12
@Hana well, never say never: `numRows = Cells.Rows.Count` and your integer is busted. Don't rely on that :) just do it right. There is no disadvantage in using `Long` just advantages. Also you don't have an implicit conversion from Long (`Rows.Count`) into Integer (`numRows`). — Pᴇʜ, Feb 19 '20 at 10:16

score 0 · Accepted Answer · answered Feb 19 '20 at 09:51

The issue is if the first filter set's a range correctly Set filterredRange then the variable contains a range.

If you now re-use the same variable for your second filter

'check second filter
On Error Resume Next
    Set filterredRange = tbl.ListColumns("Filed").DataBodyRange.SpecialCells(xlVisible)
On Error GoTo 0

and there are no visible cells, your code errors (but doesn't show a message because you suppressed it with On Error Resume Next) and your filterredRange is not re-set (this line errored and therefore filterredRange still has its original value). If a line errors this line does nothing at all (just as it was never executed).

To prevent this you must re-initialize your variable with Set filterredRange = Nothing before using it for the second filter.

vba count visible rows after filter

1 Answers1