Count Hidden and Non hiden lines in Excel.


I have a big Excel SpreadSheet and many of the lines are hidden.
I want a function that counts the hidden/NonHidden lines.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Do the cells have entries? You can use SUBTOTAL function to find unhidden rows in a range that are populated, e.g.


regards, barry
If you want a count of hidden and non-hidden lines, and you need a VBA solution because the columns with data may/may not vary and some hidden rows may not have data, etc., then this function should work in all cases.

=CountHiddenLines(rng as Range, Optional bHidden = TRUE) As Long 'defaults to counting hidden lines, so second parameter omitted or set to TRUE, otherwise set to FALSE to count non-hidden lines


=CountHiddenLines(A1) 'in a worksheet - the range A1 doesn't matter, just needs to be a cell reference
=CountHiddenLines(A1,FALSE) 'in a worksheet - the range A1 doesn't matter, just needs to be a cell reference

or in VBA:

Dim lHidden as Long
Dim lNonHidden as Long

     lHidden = CountHiddenLines(Range("A1")) 'returns hidden lines in Used Range of worksheet
     lNonHidden = CountHiddenLines(Range("A1"),FALSE) 'returns non-hidden lines in Used Range of worksheet

Here's the code you would paste in a public module of your workbook:

Option Explicit

Function CountHiddenLines(rng As Range, Optional bHidden = True) As Long
'bHidden = TRUE counts hidden lines, otherwise counts non-hidden lines
Dim r As Range
Dim l As Long
    Application.Volatile 'to capture row hiding not associated with filters
    'filters are handled via timestamp on sheet, affecting a worksheet_calculate to update the function results
    For Each r In rng.Worksheet.UsedRange.EntireRow
        If r.EntireRow.Hidden = bHidden Then
            l = l + 1
        End If
    Next r
    CountHiddenLines = l
End Function

Open in new window

Note:  for the worksheet function to work AS ROWS ARE HIDDEN or UN-HIDDEN, a timestamp on the sheet will allow filter changes to affect a calculated update of the function.  In all other cases, the Application.Volatile in the function keeps the results up to date if hiding/unhiding manually.

PS - this is one of those times I use UsedRange, as .Find and .End(xlUp) could be problematic to find the last row.

See attached.


zmauAuthor Commented:

Thanks all.
Sorry it took me a long time to response (vacation).
The solution that you had offered works and simple, but it has the disadvantages stated by Dave (dlmille).

The solution that you had offered seems to work, but I have some newbies issues.
Maybe it's because I am using Office 2007.

1) What is the "public module of my workbook" ?
     I have no idea what does this mean.
2) I have opened the attached file "countHidden-r1.xls"  but where is the code ?
    According to Excel there are no macroes in the file.
3) I do not understand why do we need the "range" parameter if it is not used.
4) What if I wanted to count beggining in line 2 (After the headings) ?

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Version of Excel should not matter, if you have Excel 2002+ or later (and perhaps even with earlier than that).

>>1) What is the "public module of my workbook" ?

This is a function that you can use in your Excel workbook.  However, you need to make that function accessible to your workbook.  So, to use it, you need to add it to a public (sometimes called "regular") module of your workbook.

Hit Alt-F11 to bring up the VBA editor.  Then, look to the left and find your workbook - you should see VBAProject(name of your workbook).  Right click on that and select INSERT->MODULE.  THEN, you can paste that code there.  Get out of the VBA Editor (you can hit the X at the top right, or File/Close on the editor screen).  Now, in that workbook, you can use the =CountHiddenLines() function as described in my initial post.

Here's a very good tip on how to add code to Excel:

>>2) I have opened the attached file "countHidden-r1.xls"  but where is the code ?

Not sure how you're determining from Excel how there are no macros in the file.  If you've downloaded the file to a trusted location ( a place where Excel allows you to run macros from ), then the macros are there.

If you look at the workbook I uploaded, you can hit Alt-F11 and find where the code was pasted - look to the left, see the VBAProject(countHidden-R1.xls) and then see Module1 that exists a few rows beneath that.

3) I do not understand why do we need the "range" parameter if it is not used.

The range parameter is to allow the function know what sheet you are working on, as it does work against that sheet.

4) What if I wanted to count beggining in line 2 (After the headings) ?

I'm happy to modify the function to add an optional parameter as the starting row.  First, let's ensure that everything is working and you have no additional questions re: items 1-3 above.


zmauAuthor Commented:

Everything is OK now  :-) .

Regardig question #2 :   I had pressed Alt+F8, and got an empty list of macroes.

>>Regardig question #2 :   I had pressed Alt+F8, and got an empty list of macroes.

Alt-F8 gives you a list of macros to run, whereas Alt-F11 opens up the VBA editor where you'll find macros and functions.  You will not see functions with Alt-F8 as they are not run as macros, but used in your worksheet as worksheet functions.

Glad everything's OK now.

I've now added a 3rd parameter to the function.  The last two are optional, but you have to create a space for the second parameter to use the third.

Here are different ways to use the function:

=counthiddenlines($A$1) 'counts hidden lines in the worksheet
=counthiddenlines($A$1,,FALSE) 'counts non-hidden lines in the worksheet

=counthiddenlines($A$1,5) 'counts hidden lines in the worksheet, starting at row 5
=counthiddenlines($A$1,5,FALSE) 'counts non-hidden lines in the worksheet

Remember, for this function to dynamically update when you make filter changes, you need to put another worksheet function that will trigger the counthiddenlines() function update (e.g., =NOW()).

SO, anywhere on the worksheet, just put =NOW() in.  Then, when you make filter changes, the count function you have will automatically update.

See attached.

zmauAuthor Commented:
Thanks a lot.
Now, is there a way to put this code/function so it is accesible for all of my worksheets/workbooks ?

Absolutely!  You would need to have either an add-in (if you are distributing the function to others, or to use yourself) or a Personal.xlsb (which can hold functions and macros that you can use from any workbook you have open on your desktop).

To add to a Personal.xlsb (a couple tips to follow):


zmauAuthor Commented:
More than I had expected.
FYI - for future, if an Expert delivers "more than expected" you can actually change the points to recognize that before you award.


zmauAuthor Commented:
I wanted to do it, but there is no way to do it in the "Accept solution" page.

Thanks again,
Actually, you asked a good question as to why a cell had to be referenced.  It does not.  We can use Application.Caller to determine what cell is calling the function.

e.g., set rng = Application.Caller <- so we don't need to reference A1 in the VBA function.


=CountHiddenLines() 'in a worksheet
=CountHiddenLines(,FALSE) 'non-hidden in a worksheet

=CountHiddenLines(5) 'hidden starting at row 5
=CountHiddenLines(5,FALSE) 'non-hidden starting at row 5

Please use the attached as the final version.

PS - if you feel strongly about points, you can request attention.  I think you can Edit the Question (at the top) to add points before the question is closed.  You might just request attention to ask how to do it in future so you're armed with that.  No worries!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zmauAuthor Commented:

I had learnt another thing from you : "request moderator's attention".
I could not edit the question, so I asked the moderator for 2 things :
1) Add points to you.
2) Enable adding points at the "Accept solution" page.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.