Table Object after filtering - Count visible rows & First visible row

[limalf]

Hi,

I have been working with tables and sometimes I do not get the right value for rows counts and first visible row (excluding headings).

I have a table object - TabResults filtered usind field 2 with criteria "Active".

Could you please share a very reliable way to:

Process 1 - Count visible cells (without heading) that gives 0 when no results are found after filtering.

Process 2 - Get the row number of the first visible row (excluding headings), and showing a msgbox "No visible row" when no visible are available.

This is pretty much the basis of my code to use a database. So it must be very solid.


Thank you very much for your help!

[PCI]

Have a look on SUBTOTAL and better send a file sample with a display of the results you want

[Norie]

This might help.

Dim lstObject As ListObject
Dim rng As Range

    Set lstObject = Sheets("Sheet1").ListObjects("TabResults")

    On Error Resume Next
    Set rng = lstObject.DataBodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Not rng Is Nothing Then
        MsgBox "First visible row: " & rng.Row

        MsgBox "No of visible cells: " & rng.Cells.Count
    Else
        MsgBox "No visible cells"
    End If

[limalf]

Thanks so much! It works very well!

[limalf]

Hi again,

I am sorry for coming back to this, but that code works fine when I have more than one visible row in the table body. With no visible cells on the table body, it give the same count as with one visible row, 1.

I am using the code specially to find if is there any entry or not and use it in case it exists. Can you please have a look on the code?
Thank you!

Also the statement does not work then there are no visible rows in the table body.

LRC = Contracts.Cells(Rows.Count, 1).End(xlUp).Row


    For x = 2 To LRC
        With Worksheets("Billing").ListObjects("tabBilling")
        On Error Resume Next
            With .Range
            .AutoFilter
            .AutoFilter 3, Contracts.Cells(x, 2)
            .AutoFilter Field:=4, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
            End With

            On Error Resume Next
            Set Rng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
            myVal = Billing.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count
            On Error GoTo 0
                If Not myVal = 1 Then
                    Billrow = Rng.Row
                    Contracts.Cells(x, 27) = Billing.Cells(Billrow, 11)
                Else
                    Contracts.Cells(x, 27) = ""
                End If
        End With
    Next x

[Norie]

Why aren't you checking if the range is Nothing?

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[limalf]

Thank you for your answer. Actually I was trying different combinations and I just pasted one different from your suggestion. Here is a sample - Can you please have a look on it? Thank you in advance!

[Norie]

The problem was non-contigent areas in the filter result.

Give this a try.

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim are As Range
Dim lngCount As Long

    With Worksheets("Sheet1").ListObjects("Table1")

        With .Range
            .AutoFilter
            .AutoFilter 2, Sheet1.Range("F2")
        End With

        On Error Resume Next
        Set Rng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0

        If Not Rng Is Nothing Then
            For Each are In Rng.Areas
                lngCount = lngCount + are.Rows.Count

            Next are
            Sheet1.Range("H2") = lngCount
            Sheet1.Range("H5") = Rng.Row
        Else
            Sheet1.Range("H2") = "none"
            Sheet1.Range("H5") = "No Row"
        End If
    End With

End Sub

PS Don't add On Error Resume Next anywhere else, if you encounter any other errors/problems post back.

[limalf]

Thank you Norie! So far so good... I know that this can be too much, but would you be able to explain why your previous solution did not work? It looked less complex than this alternative.

Anyway, thank you so much!