removing hidden fields from equation?

[djarcadian]

I have a simple equation like =SUM(F1:F28) but how can I prevent hidden cells from being added?

[Dave Peterson]

If the row is hidden as the result of an autofilter, you could use:

=subtotal(9,f1:f28)

If you're using xl2003, you could use:
=subtotal(109,f1:f28)

And that will ignore hidden rows -- both filtered and manually hidden.

======
Or you could 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

djarcadian wrote:
>
> I have a simple equation like =SUM(F1:F28) but how can I prevent hidden
> cells from being added?
>
> --
> djarcadian
> ------------------------------------------------------------------------
> djarcadian's Profile: http://www.excelforum.com/member.php...o&userid=15877
> View this thread: http://www.excelforum.com/showthread...hreadid=535727

--

Dave Peterson