Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting non-hidden rows (97 SR-2)

    Is there any way to count non-hidden (or hidden) rows?

    A colleague has a spreadsheet with approximately 4,800 rows, more than half of which he has hidden (manually!). He has asked for my assistance in determining how many of the rows are hidden (or non-hidden). Short of manually counting the row (not an option), how would I calculate how many rows are hidden (or, on the flip side, non-hidden).


    Thank you for your help,

    Randy

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting non-hidden rows (97 SR-2)

    That's way too ugly, John. Here's what I suggest. It just counts the number of rows (65536 on my xl97 box) and subtracts the number of visible rows. Nothing needs to be selected. It would only fail if column A was hidden. If this is a possibility, I can fix it with a couple more lines, but I tried to keep it simple. HTH --Sam
    <pre>Option Explicit
    Sub countHidden()
    With ActiveSheet
    MsgBox .Cells(1).End(xlDown).Row - _
    .Columns(1).SpecialCells(xlCellTypeVisible).Count
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting non-hidden rows (97 SR-2)

    it's a little ugly, but this should do it; select the range or entire column you want then run this:

    [Edited; somehow when I pasted this I left out MsgBox; dunno how that happened]

    Sub CountHiddenRows()
    MsgBox Intersect(Selection, ActiveSheet.UsedRange).Rows.Count - _
    Intersect(Selection, ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisi ble).Count
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting non-hidden rows (97 SR-2)

    Ugly is what I do best, Sammy. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Speaking of simplicity, couldn't we botth use

    ActiveSheet.Rows.Count
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting non-hidden rows (97 SR-2)

    Randy, you can do that without a VBA macro.

    In a given column, simply highlight from the top of the table to the bottom. The AutoCalculate feature in the Status Bar can then display how many cells (rows) are visible. AutoCalculate is to the left of NUM in the status bar. It mostly displays "Sum=" when you hightlight multiple cells with numbers in them. But you can right click "Sum=" and change it to "Count" so it will show how many cells are visible. It will not count the hidden cells.

    -Lenny

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting non-hidden rows (97 SR-2)

    Good tip! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> I had no idea you could right click the status bar =SUM and change it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting non-hidden rows (97 SR-2)

    Yeah, it's one of those kinda-hidden features. But, since version 95, it's been a godsend to me for quick answers and a double-check against formulas. I wish it had a print or copy feature, however.

    -Lenny

  8. #8
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting non-hidden rows (97 SR-2)

    John,

    Thanks for your help, but I wasn't able to compile the function ... the compiler kept complaining that [.COUNT] was an "Invalid use of property".

    Again, thanks for your time!


    Best regards,

    Randy

  9. #9
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting non-hidden rows (97 SR-2)

    Sam,

    Thanks for your input! Although I was bale to compile the code and run the macro, I kept getting the result of -59,972! Checking my worksheet, my last row is 65,536 and last bit of data is in row 4,657. I would have expected a difference of at least 60,879 or more.

    I suppose I should have requested help in calculating the number of visible (non-hidden) rows. Not being at ALL conversant in VB, I'm not able to switch the 'context' from hidden to non-hidden. Sorry to ask for the calculation. .. my mistake!

    In any event, thanks for your help!


    Best regards,

    Randy Miller

  10. #10
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting non-hidden rows (97 SR-2)

    Lenny,

    Thank you! What a simple solution (aren't they always the best?)!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    I also had not discovered that functionality ... I'll be sure to add this one to my 'Must remember" list!


    Thanks again,

    Randy

  11. #11
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting non-hidden rows (97 SR-2)

    John,

    OK ... now I was able to compile your macro! <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> Thanks for the heads up ... like I said, I'm a complete newbie!


    Thanks again,

    Randy

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting non-hidden rows (97 SR-2)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Very sorry! <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> for me.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    New Lounger
    Join Date
    Jul 2002
    Location
    Brighton, Michigan, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting non-hidden rows (97 SR-2)

    John,

    No reason to be sorry ... if it wasn't for help from you, Sam, Lenny and all the others willing to share your knowledge, I'd have even LESS hair than I do now (and never mind the color)!

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Thanks again,
    Randy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •