Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing visible cells (2000)

    How can I create a formula that will some only visible cells?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing visible cells (2000)

    Use the SUBTOTAL function.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing visible cells (2000)

    <img src=/S/sad.gif border=0 alt=sad width=15 height=15> Alas, according to Microsoft
    <hr>the SUBTOTAL function ignores only hidden data in lists that have an AutoFilter, or lists that have been subtotaled<hr>
    See <!mskb=287113>Microsoft Knowledge Base Article 287113<!/mskb>
    SUBTOTAL includes data in cells that have been hidden manually.

    This custom function will sum only visible cells in a range, but it's very inefficient for large ranges:

    Function SumVisible(oRange As Range)
    Dim oCell As Range
    For Each oCell In oRange
    If oCell.EntireColumn.Hidden = False And _
    oCell.EntireRow.Hidden = False Then
    SumVisible = SumVisible + Val(oCell)
    End If
    Next oCell
    End Function

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing visible cells (2000)

    For Argument's sake, if I hide row(s), they are omitted in the SUBTOTAL function, whichever way I hide them (filter or row, hide)

    As to the function, this is slightly more efficient <g> :

    Function JKPSumVisible(oRange As Range)
    JKPSumVisible = Application.WorksheetFunction.Sum(oRange.SpecialCe lls(xlCellTypeVisible))
    End Function

    Yours took 29 seconds on a 1000 row range, mine took 0.06 s...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing visible cells (2000)

    It's certainly faster, but does it work? Your code was the first thing that I tried, but it sums the entire range instead of just visible cells both in Excel 97 SR-2 and in Excel 2002 SP-2. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    I also tried summing each of the areas of oRange.SpecialCells(xlCellTypeVisible) and discovered that oRange.SpecialCells(xlCellTypeVisible) becomes equal to oRange when the function is running! <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing visible cells (2000)

    <IMG SRC=http://www.smilies.nl/eek2.gif alt="Duh" >
    Sheesh.

    You're correct of course.
    Sounds like a bug to me.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing visible cells (2000)

    Fastest would be:

    Define a name:

    Visible
    Refersto:
    =GET.CELL(17,INDIRECT("rc",False))>0

    Alongside the column with rows-to-be-hidden, enter =Visible (assume this is column [img]/forums/images/smilies/cool.gif[/img]
    Now use

    SUMIF(B1:B4000,True,A1:A4000)

    DON"T copy the cells with =Visible to another sheet, that will crash XL2000 and XL97.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    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: Summing visible cells (2000)

    Pure speculation on my part, but I think the problem is that "specialcells" works by making a SELECTION. I do NOT think you can change the SELECTION in a function (at least one called from excel, called in VB seems to be different!). If you write is as a SUB (for academic purposes) and put the Value somewhere, it works as expected.

    Steve

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing visible cells (2000)

    Yes, I thought about that too. The specialcells object is created to mimick the Goto-special command, which is not allowed in a udf.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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