VBA - Count Hidden Rows (in single selection)

[velohead]

Hi All,


Is it is possible to determine how many rows are hidden in a simple, single selection, eg D3:D21.

Selection will always be just a single selection

Selection will always be just one cell wide, but differing lengths (eg G4:G58 or H5:H168 etc).

Hope this all makes sense.



I guess it would be a collection type command, but I am not very good at collections in VBA.

Not sure where to start.

 

[Cubist]

Try this

Sub CountHiddenRowsInSelection()
    Dim hiddenRowCount As Long
    
    With Selection
        hiddenRowCount = .Rows.Count - .SpecialCells(xlCellTypeVisible).Count
    End With
    
    MsgBox "Number of hidden rows: " & hiddenRowCount
End Sub

 

[velohead]

Thank You Cubist, your solution works so very well indeed.

Just to take this further to learn some VBA concepts.....
Looks like the solution uses "With Selection" so I guess the VBA only does one iteration rather then many ???
I see you Dim hiddenRowCount as Long instead of integer - I assume Long is more efficient ???

 

[Cubist]

Thank You Cubist, your solution works so very well indeed.

Just to take this further to learn some VBA concepts.....
Looks like the solution uses "With Selection" so I guess the VBA only does one iteration rather then many ???
I see you Dim hiddenRowCount as Long instead of integer - I assume Long is more efficient ???

There's no looping involved.
The choice of long over int type is due to storage size. Int type can store values between -32,768 to 32,767 (2 bytes) whereas long is -2,147,483,648 to 2,147,483,647 (4 bytes). If you dim as int and hid over 32,767 rows, it will throw an overflow error.

 

[velohead]

Great stuff. Thank for your clear answer, and also time taken to solve and reply.