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
