CODEWITHSUNDEEP
excelvba
Alexander Paudak
Alexander Paudak

Reputation: 155

How to properly count visible rows in VBA Excel?

Consider this list in excel:

enter image description here

That aims to count those visible rows. So I have this Module (Macro):

Option Explicit
Dim ws As Worksheet
Dim rCount As Long, x As Long
Dim rng As Range
Sub printTest()
    Dim content As String
    
    Set ws = ThisWorkbook.Worksheets(1)
    rCount = 0
    
    For x = 4 To ws.Range("A3").CurrentRegion.Rows.Count
        If ws.Cells(x, 1).EntireRow.Hidden = False Then
            rCount = rCount + 1
            content = content & " " & ws.Cells(x, 1)
        End If
    Next x
    MsgBox "Visisble Row: " & rCount & " Content: " & content
End Sub

As you see in the Message box it does not include all my visible rows. What I am missing in my code? Hope someone can help.

Upvotes: 0

Views: 1664

Answers (1)

FunThomas
FunThomas

Reputation: 29296

Assuming that your CurrentRegion is A3:B13, ws.Range("A3").CurrentRegion.Rows.Count will return 11 (as the range contains 11 rows). Now you loop from row 4 to row 11 of the worksheet and not the range and therefore you see only the data until row 11, not 13.

The following code uses the fact that every range has again a Cells-property and loops over the cells of the Range:

Dim r As Range, x As Long, rCount As Long, content As String
Set r = ws.Range("A4").CurrentRegion
For x = 2 To r.Rows.Count
    If Not r.Cells(x, 1).EntireRow.Hidden Then
        rCount = rCount + 1
        content = content & " " & r.Cells(x, 1)
    End If
Next
Debug.Print content

An alternative is to use the SpecialCells-method with the parameter xlCellTypeVisible - that will return only the visible cells of a Range. However, be aware that this will lead to a runtime error if no cells are visible:

content = ""
Dim cell As Range
Set r = ws.Range("A10").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible)
For x = 2 To r.Cells.Count
    content = content & " " & r.Cells(x)
Next
Debug.Print content

Upvotes: 1

Related Questions