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!

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

Sorry, since your question dealt specifically with hidden cells, I just
assumed you were familiar with writting array formulas to sum based on
conditions, but didn't know how to test for the particular condition of
hidden cells. The worksheet cell formula

=SUMPRODUCT(IsVisible(A1:X1)*A1:X1)

uses my IsVisible() function to sum only the cells of A1:X1 that are not
in hidden columns. When you change the code in IsVisible() to deal with
rows then you can change the cell formula to sum down a column instead
of across a row.

In retrospect, a better name for my function would have been
IsVisibleCol(), and the suggested revision would be IsVisibleRow(), so
that both could coexist in an installed Add-In.

Jerry

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!


.

Hi Eva,
I use this sub to create an array constant, which I can use in a Sum
formula on the worksheet.
Suppose the numbers you are summing are named "myRange" in Excel. To
sum them all you would use
=Sum (myRange)
in a worksheet cell. If you run the sub below (say from a worksheet
form button) you can sum the visible rows using the worksheet formula
= Sum(if(ShownRows,myRange,0))
and enter it as an array formula (Ctrl+Shift+Enter)

This is much faster than creating a Range object consisting of the
visible rows and summing that range. It also separates the hiding task
from the summing task.

regards
Paul

Public Sub Create_Filtered_Array()
'Creates an array of true/false for a row not hidden/hidden
'array is named to be used by worksheet
'This array is used in worksheet functions to apply them to filtered
data only
Dim rgRow As Range
Dim FilterArray() As Boolean
Dim rownumber As Integer
Dim k As Integer
Dim Test_1 As Variant, L_Cert As Variant, Grade As Variant, Attendence
As Variant, Improvement As Variant
Application.ScreenUpdating = False
With Range("myRange") 'All cells in this range should be
occupied
rownumber = .Rows.Count
ReDim FilterArray(1 To rownumber, 1 To 1)
For k = 1 To rownumber
FilterArray(k, 1) = Not .Rows(k).EntireRow.Hidden
Next k
End With

Names.Add Name:="ShownRows", RefersTo:=FilterArray
'Creates a named array constant, consisting of a column of Booleans
End Sub

Dave Peterson wrote in message ...
How about this variation of Jerry's code:

Thanks Jerry, Paul, and Dave for your answers to my
question. Even better, since I have 3 different solutions
to my problem, I get to learn more! Thanks again.

-----Original Message-----
Sorry, since your question dealt specifically with hidden
cells, I just
assumed you were familiar with writting array formulas to
sum based on
conditions, but didn't know how to test for the
particular condition of
hidden cells. The worksheet cell formula

=SUMPRODUCT(IsVisible(A1:X1)*A1:X1)

uses my IsVisible() function to sum only the cells of
A1:X1 that are not
in hidden columns. When you change the code in IsVisible
() to deal with
rows then you can change the cell formula to sum down a
column instead
of across a row.

In retrospect, a better name for my function would have
been
IsVisibleCol(), and the suggested revision would be
IsVisibleRow(), so
that both could coexist in an installed Add-In.

Jerry

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!


.



.