Functions won't calculate without F2
Niek,
I found a cheat.
In each of my Hide/Unhide Odd/Enen Numbers, I called a macro that simply put
the formulae back into the cells again thus:
Sub RedoFormulae()
Range("HiddenTotal").FormulaR1C1 = "=sumhiddenrows(NumberRange)"
Range("VisibleTotal").FormulaR1C1 = "=sumvisiblerows(NumberRange)"
End Sub
It's the equivalent of pressing F2 in each of the cells calling the functions.
Cheers
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
|