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" <nicolaus@xs4all.nl> wrote in message
news:uCj1qedkFHA.3164@TK2MSFTNGP15.phx.gbl...
> 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 (Tools>Options, 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" <PeterRooney@discussions.microsoft.com> wrote in message
> news:F86DA531-7675-4DDD-B451-6711A7CB2513@microsoft.com...
>> 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
>>
>
>