Solved

Count Hidden and Non hiden lines in Excel.

Posted on 2012-04-02
14
296 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
  • 6
  • 6
14 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 66 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 41

Assisted Solution

by:dlmille
dlmille earned 334 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
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 334 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 41

Assisted Solution

by:dlmille
dlmille earned 334 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 41

Assisted Solution

by:dlmille
dlmille earned 334 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 41

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 41

Accepted Solution

by:
dlmille earned 334 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now