Summing Hidden Cells

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top