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!

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!

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!