How about this variation of Jerry's code:
Option Explicit
Function SumVisible(myRng As Range) As Double
Application.Volatile
Dim myCell As Range
Dim mySum As Double
For Each myCell In myRng.Cells
If myCell.EntireRow.Hidden = True Then
'don't add it
Else
If IsNumeric(myCell.Value) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell
SumVisible = mySum
End Function
But remember to calculate the worksheet before you trust the number. (Watch the
sum when you just hide/unhide a row).
in the worksheet: =sumVisible(a1:a10)
Eva Shanley wrote:
Jerry, thanks for the function. Sorry to bother you on
this again, but I'm pretty dense when it comes to VB.
Using this as is just give me "True" as the result. What
else do I have to do?
-----Original Message-----
Here it is. The argument can be a single cell or an
entire range (as in
an array formula). Change EntireColumn to EntireRow for
your application.
Function IsVisible(ByVal Target As Excel.Range) As Variant
' must be manually recalculated since hidding/unhiding
colums does not
trigger recalc
Dim Results()
ReDim Results(1 To 1, 1 To Target.Columns.Count)
i = 0
For Each c In Target.Columns
i = i + 1
Results(1, i) = Not c.EntireColumn.Hidden
Next c
IsVisible = Results
End Function
Jerry
Jerry W. Lewis wrote:
Not and have it automatically update as you hide or
unhided columns.
Format changes do not trigger any event that could be
used to trigger a
recalculation. I wrote an IsVisible() function, that I
can post later,
but without such an event, you will either have to
manually recalculate
when you change what is hidden.
If what is hidden will not change, why not just
reference the visible
cells?
Jerry
Eva Shanley wrote:
Is there a way, either programmatically or with a User
Defined
function, to leave hidden rows out of a sum? A user
here wants to
hide rows in various instances without having to
redefine the sum
range all the time, and does not want them included in
his total. Any
help as always is appreciated!
.
--
Dave Peterson