Counting visible rows

[dpaton05]

I have a filter that is run on my sheet. The data starts in A4 and I am trying to write the code that will determine if there is more than 1 result for the autofiler, ie. there are more visible rows eg. row 4, row 5 etc

So far I have this code but this code counts the actual cells, not just the visible ones.

                                Dim lRow As Long
                                lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
                                If lRow > 4 Then

Can someone tell me the code to count the visible rows please?

 

[Zot]

Your code will find the last row. So, it is still valid if you want to loop through 1st to last row

If you want to count how many visible row, you can

Dim Rng as range, xCell as range

For Each xCell In Rng
    If Not xCell.EntireColumn.Hidden Then
       xCount = xCount + 1
    End If
Next

I don't know if later Excel version has simple command. I'm using 2016. You should update your profile.

 

[footoo]

Sub v()
Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
End Sub

 

[Saurabhj]

Hi, Use below:

Sub visibleRow()
    Dim lRow As Long, visibleRow As Long, RowCount As Long
    lRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(xlUp).Row
    visibleRow = 0
    
    For RowCount = 5 To lRow
        If Not (Sheets("Sheet1").Range("A" & RowCount).EntireRow.Hidden) Then visibleRow = visibleRow + 1
    Next
    
    If visibleRow > 4 Then MsgBox "Yes"
End Sub

 

[Zot]

Here is a better way I think

Sub VisibleRow()

Dim j As Long
Dim rng As Range
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
Set rng = ws.Range("A1:A10").SpecialCells(xlCellTypeVisible)
j = rng.Count

End Sub

 

[dpaton05]

Thanks so much footoo