1. Summing visible cells (2000)

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

2. Re: Summing visible cells (2000)

Use the SUBTOTAL function.

3. Re: Summing visible cells (2000)

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

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

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

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

Posting Permissions

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