Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count entries in visble cells (ExcelXP)

    Is there a way (pref. in EXCEL but VBA is OK too) to count entries in a range of cells where some rows, colums are hidden? I only want the totals for visible cells, NOT for hidden cells. I'm not using an auto-filter...

    Thanks,

    EJ

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count entries in visble cells (ExcelXP)

    =SUBTOTAL(9,Range)
    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count entries in visble cells (ExcelXP)

    Thanks, but like I said: I'm NOT using an autofilter. Your suggestion does not work

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count entries in visble cells (ExcelXP)

    Mea culpa. I misread your query.

    Aladin
    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count entries in visble cells (ExcelXP)

    It can only be done with VB since you must check the hidden property of the rows and columns.
    You can not use the "trick" of using "specialcells" to just get the visible cells, since this "selection" will not work in a worksheet function (it will work for a functin in VB, but not one to add to a worksheet), so you must look at every cell individually to determine if it is "not hidden".

    Steve

    <pre>Function SumVisible(rng As Range)
    Dim rCell As Range

    SumVisible = 0
    For Each rCell In rng
    If Not (rCell.Rows.Hidden) And _
    Not (rCell.Columns.Hidden) And _
    IsNumeric(rCell.Value) Then _
    SumVisible = SumVisible + rCell.Value
    Next
    Set rCell = Nothing
    Set rng = Nothing
    End Function</pre>


Posting Permissions

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