Thats right, this is the behaviour under Excel 2000 and below, but not
Excel 2002 and greater. Your macro works under Excel 2003.


Peter Rooney wrote:
Dave,

I did and it doesn't work - whether you put the calculate command in the
function or the hide/unhide macros. It doesn't even work if you press F9 to
recalculate manually. You have to F2/Enter the cells containing the functions
(or, in VBA, re-enter the formulae)

Puzzling, isn't it?

Pete



"Dave Peterson" wrote:


I saw that in your other post.

I think I would have just recalculated in the subroutine that hid/unhid the
rows.


Peter Rooney wrote:

Dave,

I modified my hide/unhide formulae to re-enter the custom functions into
their appropriate cells and it works OK. Can't wait for my Office XP upgrade!
:-)

Thanks

Pete

"Dave Peterson" wrote:


You could add:

application.volatile

At the top of your sub, but that just means that the function will calculate the
next time excel calculates. (and I'd remove the calculate from your UDF--I bet
it doesn't help!)

But hiding a row doesn't force calculation (until you get to xl2003). Maybe you
can add an "application.calculate" to the routine that hides the rows???

(In xl2003, =subtotal() has been enhanced to be able to ignore manually hidden
rows: =subtotal(109,a1:a22)

(100 + # means to ignore the manually hidden row.)

Peter Rooney wrote:

Hmm. Perhaps I should have been a little more specific with the problem.

I have a range of cells containing the numbers 1-10, with 4 buttons that run
macros to hide/unhide the rows containg the odd or even numbers.

It's when I run these macros to hide 1/3/5/7/8 (displaying 2/4/6/8/10) or
hide 2/4/6/8/10 (displaying 1/3/5/7/9) that the function doesn't recalculate.

There is a SumVisibleRows function too that does the opposite of
SumHiddenRows, displaying a total for all the visible cells in the range thus:

Function SumVisibleRows(TheVisibleRange)
Dim VisibleTotal As Long
Dim VisibleCell As Range

VisibleTotal = 0
For Each VisibleCell In TheVisibleRange
If VisibleCell.EntireRow.Hidden = False Then
VisibleTotal = VisibleTotal + VisibleCell.Value
End If
Next
SumVisibleRows = VisibleTotal
Calculate
End Function

If I change the value of a visible cell in the list, SumVisibleCells
correctly calculates the new value without F2, but neither formula calculates
new totals on its own when the hide/unhide macros are run.

Should this be some sort of Worksheet_Change macro instead, and if so, can
anyone advise on the syntax for picking up changes to the hidden property of
a row?

Thanks again

Pete

"Peter Rooney" wrote:


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


--

Dave Peterson


--

Dave Peterson