Summing visable cells only

[Guest]

I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to
have a formula at the bottom of the rows that sums only the visable cells
(non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).

 

[Guest]

=SUBTOTAL(109,A1:A8000) will give you what you need.

 

[Guest]

Dave, thanks for your quick response but when I tried that formula, I got
#VALUE! as a result. I am familiar with =SUBTOTAL(9,A1:A8000) but what is
"109". I cannot get that to work.

 

[Guest]

What version of Excel are you using? "109" should ignore all hidden values,
where as just "9" includes hidden values (in 2003 atleast)

 

[Guest]

Ahh, that may be my problem. I am using Excel 2002 version here. Is there
anyway you (or anyone) know of to do this with the 2002 version. thanks
again!!!

 

[Dave Peterson]

You can use a userdefined function:

Option Explicit
Function SumVisible(rng As Range) As Double

Application.Volatile True

Dim myCell As Range
Dim mySum As Double

mySum = 0
For Each myCell In rng.Cells
If myCell.RowHeight = 0 _
Or myCell.ColumnWidth = 0 Then
'do nothing
Else
If Application.IsNumber(myCell.Value) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumVisible = mySum

End Function

When I hid/unhid a column, the UDF didn't recalculate (xl2003). You may want to
force a recalc before you trust the results.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumvisible(a1:a8000)

 

[Guest]

You rule!! This did the trick. I couldn't figure out how to use a macro to
recogize a hidden cell. I didn't think of using "myCell.RowHeight = 0".
Thanks again!!!