-----Original Message-----
Maybe you'll like to try the 2 UDFs below in the interim,
one posted by Jim Rech, the other by John Walkenbach:
Option (a) - UDF below by Jim Rech:
[ Link:
http://tinyurl.com/3tes6 ]
Steps:
Press Alt+F11 to go to VBE
Click Insert > Module
Copy > paste everything within the dotted lines below
into the whitespace on the right
-------begin vba-----
Function SumVis(Rg As Range) As Double
'By Jim Rech in .misc Jan 2002
Dim Cell As Range
Application.Volatile
For Each Cell In Rg
If Cell.EntireRow.Hidden = False Then
''If only rows can be hidden remove this If
If Cell.EntireColumn.Hidden = False Then
SumVis = SumVis + Cell.Value
End If ''and this End If
End If
Next
End Function
-------endvba------
Press Alt+Q to get back to Excel
In Excel:
Put in say, B2: =sumvis(C2:G2)
Copy B2 down
Col B will return the desired sum of the visible cols within C to G
(but you'll need to force a recalc by pressing F9 - see notes below)
Jim's notes on his UDF:
"Note that even when calc mode is automatic, Excel will
not do a recalc when a row is hidden or unhidden.
You'd have to trigger it with an F9 after hiding/unhiding a row,
or by some other change like entering data that causes a recalc."
---
Option (b) - UDF* below by John Walkenbach
[Link:
http://tinyurl.com/5egtg ]
which, as John puts it ..
"works just like the SUM function, and supports multiple arguments"
i.e. you can put in B2 something like
: =sumvisible(C2:G2,I2:K2)
and just copy down (for multiple ranges)
[Press F9 to force a recalc, as for Jim's UDF]
*lines are intentionally un-indented
to minimise risk of line wraps
(my apologies to John <bg>)
-------begin vba-----
Function SUMVISIBLE(ParamArray number())
' By John Walkenbach in .misc Jan 2002
' Returns the sum of visible cells in a range
Dim Cell As Range, i As Long
Application.Volatile
SUMVISIBLE = 0
For i = 0 To UBound(number)
If Not IsError(number(i)) Then
If TypeName(number(i)) = "Range" Then
Set number(i) = Intersect(number(i).Parent.UsedRange, number(i))
For Each Cell In number(i)
If IsError(Cell) Then SUMVISIBLE = Cell: Exit Function
If Not Cell.EntireRow.Hidden And Not Cell.EntireColumn.Hidden Then
SUMVISIBLE = SUMVISIBLE + Evaluate(Application.Sum (Cell))
End If
Next Cell
Else
SUMVISIBLE = SUMVISIBLE + Evaluate(Application.Sum (number(i)))
End If
End If
Next i
End Function
-------endvba------
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Hi Frank,
I am using Excel 97.
Using VBA is a possibility.
I am however trying to apply this to several hundred rows.
Is this still a possibility?
.
