SubTotal on Column Hiding

[GopalaKrishnaJ]

Subutotal(109,A1:A5) is working and giving me the visible result only
But Subtotal(109,A1:D1) is not working if hiding columns, Still giving me the total result.

Please help me out with a formula to get the sum of only those cells in a row if any columns got hidden

Thanks a lot

 

[cyrilbrd]

Hi,
as stated in Excel help:
"The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal."

To sum visible Columns use this:

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

Then type =Sum_Visible_Cells(A1:D1) instead of =SUBTOTAL(109,A1:D1)

 

[GopalaKrishnaJ]

Thanks Cylibrd. But the recalculation wont happen if we just make a hiding to columns. I tried with sheet change events availble. But of no luck. If i change any cell value or any selection change then the UDF working.

 

[cyrilbrd]

please post a small sample of your data with expected results when selected columns are hidden.
I am unable to duplicate your problem as the udf works fine here, or at least it does what it is supposed to do. Sum the values that are visible regardless of their range, Rows, Columns or Array.

 

[GopalaKrishnaJ]

I did same kind of UDF, but the execution of UDF happens when we use Application.Calcualte some how. Its not executing automatically on hiding columns. None of the worksheet events too not getting fired on hiding of columns

 

[EngineerToast]

Thanks Cylibrd. But the recalculation wont happen if we just make a hiding to columns. I tried with sheet change events availble. But of no luck. If i change any cell value or any selection change then the UDF working.

This is now a pretty old thread but, for future wanderers that come across this page, here's the solution: Start the UDF with

Application.Volatile

to make it recalculate with the rest of the sheet.

Function IsVisible(rng As Range) As Boolean
    Application.Volatile
    If rng.Rows.Hidden Or rng.Columns.Hidden Then IsVisible = False Else IsVisible = True
End Function

 

[Rolly_Sefu]

Hy, I have tried to add the extra lines but the value will not update only if I recalculate the sheet (or f2 + enter, or change a cell value) and if I change sheets and come back I get a #value error.

Any ideas ?
Thanks

This is now a pretty old thread but, for future wanderers that come across this page, here's the solution: Start the UDF with

Application.Volatile

to make it recalculate with the rest of the sheet.

Function IsVisible(rng As Range) As Boolean
    Application.Volatile
    If rng.Rows.Hidden Or rng.Columns.Hidden Then IsVisible = False Else IsVisible = True
End Function