Leave hidden rows out of sum
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!
|