Functions won't calculate without F2
Thanks for giving it a go, anyway, Niek! :-)
Pete
"Niek Otten" wrote:
I'm afraid even that won't work. You'll have to include a dummy argument and
supply NOW() as value to get the function recalculated
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Niek Otten" wrote in message
...
Hi Peter,
If you change the value of any of the cells in the hidden range, the
function should recalculate, unless you happen to have Manual calculation
switched on (ToolsOptions, Calculation tab).
But changing a cell form visible to hidden does not trigger a
recalculation, not even if you include Application.Volatile.
You could force a recalc by including a volatile function in the formula,
like in
=SumHiddenRows(A1:A10)+(now()*0)
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Peter Rooney" wrote in message
...
Good morning all!
I wrote a simple function to calculate the values of all hidden cells in
a
range.
It works fine, except that I have to edit the formula with [F2] and
[Enter]
before it displays the correct answer - otherwise, it just displays the
result of the previous calculation.
Can anyone help, please?
Thanks in advance
Pete
Function SumHiddenRows(TheHiddenRange)
Dim HiddenTotal As Long
Dim HiddenCell As Range
HiddenTotal = 0
For Each HiddenCell In TheHiddenRange
If HiddenCell.EntireRow.Hidden = True Then
HiddenTotal = HiddenTotal + HiddenCell.Value
End If
Next
SumHiddenRows = HiddenTotal
Calculate
End Function
|