Learned: Spreadsheet calculations that update totals when filtering!

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.

Advertisements
This entry was posted in Interactive Conversation and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s