VBA or formula - Subtotal of Non-Hidden columns

[FanofExcel18]

Hi,

Have a report, totals represented in cells b3:ac3. I need to hide columns at times and would like the subtotals.

I tried this thread but can't get it to work. Can anyone help?

https://www.mrexcel.com/forum/excel-questions/655374-subtotal-column-hiding.html

 

[Finalfight40]

Not sure of how to make it recalculate automatically but if you don't get any other options, you could add a button that will refresh the calculations for you when you press it.

The Function would look like:

[SIZE=2][FONT=arial]Function Sum_Visible_Cells(Cells_To_Sum As Object)[/FONT][/SIZE]
[SIZE=2][FONT=arial]    Application.Volatile[/FONT][/SIZE]
[SIZE=2][FONT=arial]    For Each cell In Cells_To_Sum[/FONT][/SIZE]
[SIZE=2][FONT=arial]        If cell.Rows.Hidden = False Then[/FONT][/SIZE]
[SIZE=2][FONT=arial]            If cell.Columns.Hidden = False Then[/FONT][/SIZE]
[SIZE=2][FONT=arial]                Total = Total + cell.Value[/FONT][/SIZE]
[SIZE=2][FONT=arial]            End If
[/FONT][/SIZE]
[SIZE=2][FONT=arial]        End If[/FONT][/SIZE]
[SIZE=2][FONT=arial]    Next[/FONT][/SIZE]
[SIZE=2][FONT=arial]    Sum_Visible_Cells = Total[/FONT][/SIZE]
[SIZE=2][FONT=arial]End Function[/FONT][/SIZE]

(credit to cyrilbrd)

And you could have another sub which you assign to a button which could be something like this:

[SIZE=2][FONT=arial]Sub Refresh()[/FONT][/SIZE]

[SIZE=2][FONT=arial]ActiveSheet.EnableCalculation = False[/FONT][/SIZE]
[SIZE=2][FONT=arial]ActiveSheet.EnableCalculation = True[/FONT][/SIZE]
[SIZE=2][FONT=arial]
[/FONT][/SIZE]
[SIZE=2][FONT=arial]End Sub[/FONT][/SIZE]

 

[FanofExcel18]

Not sure of how to make it recalculate automatically but if you don't get any other options, you could add a button that will refresh the calculations for you when you press it.

The Function would look like:

[SIZE=2][FONT=arial]Function Sum_Visible_Cells(Cells_To_Sum As Object)[/FONT][/SIZE]
[SIZE=2][FONT=arial]    Application.Volatile[/FONT][/SIZE]
[SIZE=2][FONT=arial]    For Each cell In Cells_To_Sum[/FONT][/SIZE]
[SIZE=2][FONT=arial]        If cell.Rows.Hidden = False Then[/FONT][/SIZE]
[SIZE=2][FONT=arial]            If cell.Columns.Hidden = False Then[/FONT][/SIZE]
[SIZE=2][FONT=arial]                Total = Total + cell.Value[/FONT][/SIZE]
[SIZE=2][FONT=arial]            End If
[/FONT][/SIZE]
[SIZE=2][FONT=arial]        End If[/FONT][/SIZE]
[SIZE=2][FONT=arial]    Next[/FONT][/SIZE]
[SIZE=2][FONT=arial]    Sum_Visible_Cells = Total[/FONT][/SIZE]
[SIZE=2][FONT=arial]End Function[/FONT][/SIZE]

(credit to cyrilbrd)

And you could have another sub which you assign to a button which could be something like this:

[SIZE=2][FONT=arial]Sub Refresh()[/FONT][/SIZE]

[SIZE=2][FONT=arial]ActiveSheet.EnableCalculation = False[/FONT][/SIZE]
[SIZE=2][FONT=arial]ActiveSheet.EnableCalculation = True[/FONT][/SIZE]
[SIZE=2][FONT=arial]
[/FONT][/SIZE]
[SIZE=2][FONT=arial]End Sub[/FONT][/SIZE]

I'm getting #VALUE ! back.

 

[Finalfight40]

Strange its working for me. Are you entering these in the visual basic workspace and then using:

=Sum_Visible_Cells(b3:ac3)

on the worksheet?

 

[FanofExcel18]

Strange its working for me. Are you entering these in the visual basic workspace and then using:

=Sum_Visible_Cells(b3:ac3)

on the worksheet?

Yes
Here is my VBA:
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

Total I have for all cells involved is 103, when I hide all but 3 columns it should be 13 (8,0,5). The 103 is showing (I hit calculate on manual, So i accept full responsibility for VALUE), but it would go to 13. I did put the refresh code you suggested, stays at 13.