software

I have a simple equation like =SUM(F1:F28) but how can I prevent hidden
cells from being added?--
djarcadian
------------------------------------------------------------------------
djarcadian's Profile: www.excelforum.com/member.php...oamp;userid=15877
View this thread: www.excelforum.com/showthread...hreadid=535727If 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:
www.mvps.org/dmcritchie/excel/getstarted.htm

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

--

Dave Peterson