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
>