VBA - Count Hidden Rows (in single selection)

velohead

Board Regular
Joined
Aug 22, 2007
Messages
219
Office Version
  1. 365
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try this
VBA Code:
Sub CountHiddenRowsInSelection()
    Dim hiddenRowCount As Long
    
    With Selection
        hiddenRowCount = .Rows.Count - .SpecialCells(xlCellTypeVisible).Count
    End With
    
    MsgBox "Number of hidden rows: " & hiddenRowCount
End Sub
 
Upvote 0
Solution
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 ???
 
Upvote 0
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.
 
Upvote 0
Great stuff. Thank for your clear answer, and also time taken to solve and reply.
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,699
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top