Ever created a spreadsheet with calculations on a long list, and then experienced frustration when filtering your list because the calculated totals don’t change when cells are hidden? Yeah me too*.
I did some searching and found these two macros that directly solve the problem. You’ll need them both, and remember to save your worksheet in a macro friendly format.
This function only reads visible cells:
Function Vis(Rin As Range) As Range 'Returns the subset of Rin that is visible 'Example =SUM(G15:G30) becomes =SUM(VIS(G15:G30)) Dim Cell As Range Application.Volatile Set Vis = Nothing For Each Cell In Rin If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then If Vis Is Nothing Then Set Vis = Cell Else Set Vis = Union(Vis, Cell) End If End If Next Cell End Function
This function replaces COUNTIF to only tally visible cells:
Function COUNTIFv(Rin As Range, Condition As Variant) As Long 'Same as Excel COUNTIF worksheet function, except does not count 'cells that are hidden Dim A As Range Dim Csum As Long Csum = 0 For Each A In Vis(Rin).Areas Csum = Csum + WorksheetFunction.CountIf(A, Condition) Next A COUNTIFv = Csum End Function
Happy spread-sheeting, if there is such a thing.
* I’m currently working on a huge VOC project documenting user’s pain-points, that automatically generates heat maps of the pain-points as they apply to parts of the end to end process.