On Wed, 12 Oct 2005 06:02:39 -0500, bucketheaduk
wrote:


Hey

I'm looking for a way to sum rows but ignore the adjacent rows which
are hidden. excel is adding the stuff in the unhide rows (which is
what i want) but also adding the hidden row numbers too (which i don't
want).

cheers for the help.

If the rows are hidden as a result of the list being filtered, you could use
the SUBTOTAL(9,rng) function.

If the rows ae hidden by some other method, then you can use a UDF (user
defined function) written in VBA.

This is from support.microsoft.com
====================
Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function
========================

To enter this, <alt<F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code into the window that opens.

Return to your worksheet and enter the formula:

=Sum_Visible_cells(cell_ref) into some cell.



--ron