Hiding/unhiding a row will force a recalc in xl2003, but I think that's because
of the added parms in =subtotal().
I don't have earlier versions of excel to test on, but if F9 didn't force a
recalc, then maybe using ctrl-alt-f9 would be more effective.
In either case, I'd force a recalc before I trusted either procedures.
BizMark wrote:
The problem with UDFs is that unless one of its parameters changes, it
will not recalculate. By parameters changing, we mean values; not the
format properties of the cells containing the values.
Therefore, a change of any format (including row height; a row height
of 0 indicates 'hidden') will not recalculate.
Even pressing F9 won't change it. One way around this is to add a
dummy date parameter to the function, and simply pass NOW() to it. As
time is constantly changing, the formula will always be earmarked by
Excel as 'requiring recalculation' each time F9 is pressed or other
formulae are recalculated. The formula itself doesn't actually need to
do anything with that parameter, but as long as it's declared, it will
recalculate.
Notwithstanding all the above, I would say the best thing to do is use
a routine which passes back a RANGE object containing only the visible
cells. That way, you can do anything you want with those cells - not
just SUM. The problem with the above example is that you would have to
create a similar function for every possible type of operation you might
want - multiplying, averaging, vlookup, etc.
The following routine however allows you to specify a range as
VISRANGE(address) and pass it to any built-in (or user-defined)
function which accepts a range parameter.
Public Function VisRange(xRange As Range) As Range
Dim xBuildRange As Range
For Each xCell In xRange.Cells
bHidden EQUALS (xCell.EntireColumn.Hidden) Or
(xCell.EntireRow.Hidden)
If Not bHidden Then
If TypeName(xBuildRange) EQUALS "Nothing" Then
Set xBuildRange EQUALS xCell
Else
Set xBuildRange EQUALS Union(xBuildRange, xCell)
End If
End If
Next xCell
Set VisRange EQUALS xBuildRange
End Function
You can then perform a SUM on the visible cells in range A1:A100 by
entering the formula =SUM(VISRANGE(A1:A100)).
Regards,
MB
Dave Peterson Wrote:
If the formula returns a number, then I think you did everything ok.
But if you've hidden/unhidden any rows/columns, then formula may not
have
recalculated.
Try hitting F9 to see if that helps.
Carol wrote:
Hi Me Again
I've got the spreadsheet to accept SUMVISIBLE and to show a total now
but
it's not correct it adds in the hidden rows as well
What am I doing wrong?
Thanks
"Dave Peterson" wrote:
The code is everything between (and including)
Option Explicit
....
End Sub
And if you read that link or follow those instructions, you may find
it not so
difficult.
The only other way I know is to upgrade to xl2003.
Carol wrote:
Thanks for that but is there an easier way as I don't understand how
to
'paste the code in there'
What is the code??
Yes I know I'm dim
"Dave Peterson" wrote:
You'll need a little user defined function that does the work for
you:
Option Explicit
Function SumVisible(rng As Range)
Application.Volatile
Dim myTotal As Double
Dim myCell As Range
myTotal = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If myCell.EntireRow.Hidden = False _
And myCell.EntireColumn.Hidden = False Then
myTotal = myTotal + myCell.Value
End If
End If
Next myCell
SumVisible = myTotal
End Function
If you're new to macros, you may want to read David McRitchie's
intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Into a test cell and type:
=sumvisible(a1:a100)
Be aware that some versions of excel won't do a calculation when
you hide a
row. So your results could be one calculation behind. Force a new
recalc
before you trust that answer.
Carol wrote:
Thanks but we don't autofilter we hide the row(s) in question need
a total.
Anybody else any views please???
Carol
"Dave Peterson" wrote:
If my memory is correct, those 100 series numbers were added in
xl2003.
=subtotal() will ignore rows hidden by an autofilter, though.
Carol wrote:
How do I use SUM or SUBTOTAL ignoring the hidden rows.
I have tried using 109 but it only comes up with VALUE
I am using XP Small Business
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
BizMark
--
Dave Peterson