?
Solved

Count Hidden and Non hiden lines in Excel.

Posted on 2012-04-02
14
Medium Priority
?
305 Views
Last Modified: 2012-04-28
Hi!

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


Thanks
Zmau
0
Comment
Question by:zmau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
14 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 264 total points
ID: 37797416
Do the cells have entries? You can use SUBTOTAL function to find unhidden rows in a range that are populated, e.g.

=SUBTOTAL(103,A2:A1000)

regards, barry
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1336 total points
ID: 37804145
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

Usage:

=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.

Cheers,

Dave
countHidden-r1.xls
0
 
LVL 1

Author Comment

by:zmau
ID: 37833608
Hi!

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

Dave,
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) ?



Zmau
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1336 total points
ID: 37833936
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:
http://www.contextures.com/xlvba01.html

>>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.

Cheers,

Dave
0
 
LVL 1

Author Comment

by:zmau
ID: 37834058
Dave,

Everything is OK now  :-) .

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




Thanks
zmau
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1336 total points
ID: 37834102
>>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.

Dave
Dave
countHidden-r2.xls
0
 
LVL 1

Author Comment

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


Thanks
zmau
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1336 total points
ID: 37834180
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):

http://www.rondebruin.nl/personal.htm
http://office.microsoft.com/en-us/excel-help/copy-your-macros-to-a-personal-macro-workbook-HA102174076.aspx

Cheers,

Dave
0
 
LVL 1

Author Comment

by:zmau
ID: 37834232
More than I had expected.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37834243
FYI - for future, if an Expert delivers "more than expected" you can actually change the points to recognize that before you award.

Enjoy!

Dave
0
 
LVL 1

Author Comment

by:zmau
ID: 37834260
Hi
I wanted to do it, but there is no way to do it in the "Accept solution" page.

Thanks again,
0
 
LVL 42

Accepted Solution

by:
dlmille earned 1336 total points
ID: 37834280
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.

Usage:

=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!

Dave
countHidden-r3.xls
0
 
LVL 1

Author Comment

by:zmau
ID: 37834343
Thanks

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.


Thanks
zmau
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question