Count Filtered row

[gnissen]

Hi all
When you apply a filter, what is the code to just count the lines displayed and ignore the hidden rows

 

[Cubist]

What are you counting specifically?
The SUBTOTAL function with the first parameter starting with 100+ ignores the hidden rows. See MS Documentation.

 

[gnissen]

Hi I am filtering out a part number and showing all data along that row, sometimes there can be 10 rows of data but from different locations. All I would like to do is count those 10 row that are displayed. But it's counting all rows hidden and viewable.
Regards
Greg

 

[Cubist]

You can try this. Select the range you want to count then run the macro.

Sub VisibleRowsInSelection()
    Dim rng As Range
    Dim c As Range
    Dim rowCount As Long
    Dim uniqueRows As Collection
    
    Set uniqueRows = New Collection
    
    On Error Resume Next
    For Each c In Selection.SpecialCells(xlCellTypeVisible)
        uniqueRows.Add c.Row, CStr(c.Row)
    Next c
    On Error GoTo 0
    
    rowCount = uniqueRows.Count
    MsgBox "Number of visible rows: " & rowCount
End Sub

 

[gnissen]

Sub VisibleRowsInSelection() Dim rng As Range Dim c As Range Dim rowCount As Long Dim uniqueRows As Collection Set uniqueRows = New Collection On Error Resume Next For Each c In Selection.SpecialCells(xlCellTypeVisible) uniqueRows.Add c.Row, CStr(c.Row) Next c On Error GoTo 0 rowCount = uniqueRows.Count MsgBox "Number of visible rows: " & rowCount End Sub

Good morning and thank you for your reply, unfortunately this is not quite what i am after. As per the image attached, when i filter my sheet and i am left with the result, i would like to count the lines and display it in Range("G2").value every time the reset my sheet