Summing Hidden Cells

[Matt]

I'm trying to enter a formula in a macro that will sum all hidden cells within a particular column, is this possible? If so, can someone supply the code?

Many thanks

Matt

[Dave Hawley]

Hi Matt

I could write you a UDF for this but quite frankly thed formula below would be FAR more efficient

=SUM($A$1:$A$10000)-SUBTOTAL(9,$A$1:$A$10000)

[Matt]

Thanks Dave, I have actually hidden rows through using code,

Dim iRow As Long
iRow = 2
Do While Cells(iRow, 1) <> ""
If Cells(iRow, 1) <> "Other" Then
Rows(iRow & ":" & iRow).Hidden = True
End If
iRow = iRow + 1

The formula you suggested does not appear to be working. Will the formula only work if the hidden cells are filtered rather than hidden through code. If so, do you have any other suggestions.

Thanks again

Matt

[Dave Hawley]

Ok, the SUBTOTAL will only work on Filtered cells (which truth be known you should use in you code). But anyway use this to hide the rows:

Dim iRow As Long
iRow = 2

For iRow = 2 To Range("A2", Range("A2").End(xlDown)).Row
Rows(iRow & ":" & iRow).Hidden = (Cells(iRow, 1) = "Other")
Next i

If you don't get your UDF by the morning let me know.

[Matt]

Dave

Sorry to be a complete pain in the ****, Have tried to hide the cells as you suggested and have the following error on the last line "Next i"

"invalid next control variable reference", any ideas?

thanks

Matt

[nisht]

Try with this... But still i think Dave has give good solution by using builtin formula..

Function visiblecelltotal(totalrange As Range) As Double
Dim c As Range
Application.Volatile

For Each c In totalrange

' set value to TRUE if you want total of
' hidden rows


If c.EntireRow.Hidden = False Then

visiblecelltotal = visiblecelltotal + c.Value

End If

Next c

End Function

[Dave Hawley]

Sorry Matt, my fault

Dim iRow As Long

For iRow = 2 To Range("A1").End(xlDown).Row
Rows(iRow).EntireRow.Hidden = (Cells(iRow, 1) = "Other")
Next iRow


May need to change the range references to suit.

[Matt]

Thanks Dave